PostgreSQL如何提取jsonb数组内指定字段值并拼接为新数组

最终实现SQL #

如果你要将整张表所有data数组里的table字段聚合为一个数组，使用以下语句：

SELECT jsonb_agg(elem ->> 'table') AS food_tables
FROM orders, jsonb_array_elements(data) AS elem;

如果需要按orders表的行维度返回，每行对应自身data字段提取的桌号数组（假设表主键为id），使用以下语句：

SELECT o.id, jsonb_agg(elem ->> 'table') AS food_tables
FROM orders o
CROSS JOIN LATERAL jsonb_array_elements(o.data) AS elem
GROUP BY o.id;

相关逻辑说明 #

jsonb_array_elements()作用机制 #

它是PostgreSQL内置的集合返回函数，作用是将jsonb类型的数组拆分为多行，每个数组元素对应一行结果。
以上面你给出的data数组为例，执行jsonb_array_elements(data)后会得到2行结果，每行分别对应数组里的两个jsonb对象：

-- 第一行
{"food_id": "1", "table": "A12"}
-- 第二行
{"food_id": "2", "table": "A14"}

其他用到的操作符/函数说明 #

• ->>：jsonb取值操作符，用于提取jsonb对象的指定字段值，返回结果为text类型
• jsonb_agg()：jsonb聚合函数，用于将多行值聚合为一个jsonb数组

内容的提问来源于stack exchange，提问作者mokalovesoulmate