POSTGRESQL:如何查询嵌套的JSONB对象以获得筛选后的JSONB对象

Question

输入Json

{
"orderId": "order1",
"fulfillerId":"ful1",
"orderDetailsUrl":"het",
"items":[{
"decorationTechnology":"laserEngraving",
"itemId":"item1",
"productName":"Test Sku for Oracle testing"
},
{
"decorationTechnology":"laserEngraving",
"itemId":"item2",
"productName":"Test Sku for Oracle testing"
}
]
}

预期输出JSON

{
"fulfillerIds":[
"ful1"
],
"orderIds":[
"order1"
],
"itemIds":[
"item1",
"item2"
]
}

需要形成以上预期的JSON尝试使用row_to_JSON((和jsonb_array_elements((，但无法获得预期的输出

Answer 1

如果您使用的是Postgres 12或更高版本，您可以使用SQL/JON-路径查询：

select jsonb_build_object(
'orderIds', jsonb_path_query_array(the_column, '$.orderId'), 
'fulfillerIds', jsonb_path_query_array(the_column, '$.fulfillerId'),
'itemIds', jsonb_path_query_array(the_column, '$.items[*].itemId')
) as filtered
from the_table;

Answer 2

在早期版本中，它可以使用串联和构建数组：

WITH t1 AS
(
SELECT '{ "orderId": "order1", "fulfillerId":"ful1", "orderDetailsUrl":"het", "items":[{ "decorationTechnology":"laserEngraving", "itemId":"item1", "productName":"Test Sku for Oracle testing" }, { "decorationTechnology":"laserEngraving", "itemId":"item2", "productName":"Test Sku for Oracle testing" } ] } '::jsonb AS field
)
SELECT jsonb_build_object('orderId', json_build_array (field->>'orderId')) || jsonb_build_object('fulfillerId', json_build_array (field->>'fulfillerId')) || jsonb_build_object('orderDetailsUrl', json_build_array (field->>'orderDetailsUrl')) AS jsonb_result
FROM t1
;