在我的jsonb列中有这样一个json:
{
"emails": [
{
"email": {
"id": "a8399412-165e-4601-824f-a55f631ad471",
"value": "test@gmail.com"
}
},
{
"email": {
"id": "fa09d9a7-a36a-42a4-8627-66b7554ce82e",
"value": "test1@gmail.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"
}
}
等等。我要求得到元素列表作为有限制的键和值对,我做了一个研究,尝试了postgres的不同功能,我写了下面的查询:
select response.* from my_table t, jsonb_each_text(jsonb_column) as response;
如果我这样做,我只得到根元素,如email、firstname和lastname,但我也需要内部元素以及它们的值,如下所示:
Key | value
------- ---------
"email" : {"id": "a8399412-165e-4601-824f-a55f631ad471","value": "test@gmail.com"}
"email" : {"id": "fa09d9a7-a36a-42a4-8627-66b7554ce82e","value": "test1@gmail.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条答案
按热度按时间k5ifujac1#
你可以用
jsonb_array_elements()
函数,并按组合查询UNION ALL
```SELECT 'email' AS key, je.* ->> 'email' AS value
FROM my_table
CROSS JOIN jsonb_array_elements(jsonb_column->'emails') AS je
UNION ALL
SELECT 'address', ja.* ->> 'address'
FROM my_table
CROSS JOIN jsonb_array_elements(jsonb_column->'Address') AS ja
UNION ALL
SELECT 'lastName', (jsonb_column->'lastName')::text
FROM my_table
UNION ALL
SELECT 'firstName', (jsonb_column->'firstName' )::text
FROM my_table