PostgreSQL中如何将多行jsonb数组合并为单个数组

你需要先将每行的jsonb数组拆分为单个元素，再聚合为一个大数组，修正后的SQL语句如下：

INSERT INTO data (concatenated_json)
SELECT jsonb_agg(elem) AS concatenated_json
FROM test
CROSS JOIN jsonb_array_elements(test.observation) AS elem;

原语句问题说明 #

你之前的jsonb_agg(observation)会把每行的整个observation数组当作一个独立元素，最终生成的是数组嵌套数组的结构（例如[[{"a":1},...], [{"d":3},...]]），不符合需求。

修正逻辑 #

1. 使用jsonb_array_elements(test.observation)将每行的jsonb数组拆分成多行独立的jsonb元素；
2. 通过CROSS JOIN（或简写为逗号）关联原表与拆分后的元素记录；
3. 最后用jsonb_agg(elem)将所有拆分出来的单个jsonb元素聚合为一个大数组。

如果需要合并时去除重复的jsonb元素，可以在聚合时添加DISTINCT：

INSERT INTO data (concatenated_json)
SELECT jsonb_agg(DISTINCT elem) AS concatenated_json
FROM test
CROSS JOIN jsonb_array_elements(test.observation) AS elem;

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