Как получить внутренние элементы столбца JsonB в Postgres

#sql #json #post&resql #jsonb

Вопрос:

У меня в столбце JsonB есть такой Json:

    {
    "emails": [
    {
    "email": {
    "id": "a8399412-165e-4601-824f-a55f631ad471",
    "value": "test@&mail.com"
    }
    },
    {
    "email": {
    "id": "fa09d9a7-a36a-42a4-8627-66b7554ce82e",
    "value": "test1@&mail.com"
    }
    }
    ],
"Address": [
    {
    "address": {
    "id": "a8399412-165e-4601-824f-a55f631ad471",
    "addressLine1": "Line1"
    }
    },
    {
    "address": {
    "id": "fa09d9a7-a36a-42a4-8627-66b7554ce82e",
    "addressLine2": "Line2"
    }
    }
    ],
    "lastName": {
    "id": "bc10a5a9-04ff-4a00-b167-ac3232e5cb89",
    "value": "LastName"
    },
    "firstName": {
    "id": "4ccdd400-2586-4a7f-9379-aff4d1f5d9d6",
    "value": "FirstName"
    }
    }

и так далее. Мое требование получить список элементов в виде пар ключей и значений с ограничением, я провел исследование, попробовал различные функции post&res и написал приведенный ниже запрос :

 select response.*  from my_table t, jsonb_each_text(jsonb_column) as response;

Если мне это понравится, я получу только корневые элементы, такие как электронные письма, имя и фамилия, но мне также нужны внутренние элементы вместе с их значениями, как показано ниже :

  Key            |  value
-------           ---------
"email"     :   {"id": "a8399412-165e-4601-824f-a55f631ad471","value": "test@&mail.com"} 
"email"     :   {"id": "fa09d9a7-a36a-42a4-8627-66b7554ce82e","value": "test1@&mail.com"}
"lastName"  :   {"id": "bc10a5a9-04ff-4a00-b167-ac3232e5cb89","value": "LastName"}
"firstName" :   {"id": "4ccdd400-2586-4a7f-9379-aff4d1f5d9d6","value": "FirstName"}
 "address"  :   {"id": "a8399412-165e-4601-824f-a55f631ad471", "addressLine1": "Line1"}
 "address"  :   {"id": "a8399412-165e-4601-824f-a55f631ad471", "addressLine2": "Line2"}

Ответ №1:

Вы можете использовать jsonb_array_elements() функцию и объединять запросы с помощью UNION ALL

 SELECT 'email' AS key, je.* -&&t;&&t; 'email' AS value
  FROM my_table
 CROSS JOIN jsonb_array_elements(jsonb_column-&&t;'emails') AS je
UNION ALL 
SELECT 'address', ja.* -&&t;&&t; 'address'
  FROM my_table
 CROSS JOIN jsonb_array_elements(jsonb_column-&&t;'Address') AS ja
UNION ALL 
SELECT 'lastName', (jsonb_column-&&t;'lastName')::text 
  FROM my_table
UNION ALL
SELECT 'firstName', (jsonb_column-&&t;'firstName' )::text 
  FROM my_table

Demo