sql - PostgreSQL 从 JSONB 数组中选择特定的项目

Question

我有一个这样的表:

orders

order_number   products
1234           [{"upc":2434354, "title":"Widget"}]
4321           [{"upc":6434556, "title":"Shorts"}, {"upc":54346, title: "Shirt"}]

我想生成这样的输出

[
{order_number: 1234, upc: 2434354},
{order_number: 4321, upc: 6434556}
]

如您所见，我从数组中提取了 UPC(仅针对数组中的第一个元素)。

请注意 products 如果类型为 jsonb

我试过这个:

SELECT jsonb_array_elements(products)[0]->>upc FROM orders ORDER BY created DESC LIMIT 10

但它给出了语法错误，我不确定在 PostgreSQL 中究竟如何进行数组遍历

还有可能 products 可能是一个空数组 []

Answer 1

demo:db<>fiddle

SELECT
    jsonb_agg(
         jsonb_build_object(
             'order_number', order_number, 
             'upc', products -> 0 -> 'upc'
         )
    )
FROM
    orders

1. products -> 0 -> 'upc' 获取第一个数组元素的 upc 值
2. jsonb_build_object() 为每个包含 order_number 和 upc 值的记录构建一个 JSON 对象
3. jsonb_agg()将这些JSON对象聚合成一个JSON数组