如何使用Postgres扁平化嵌套JSONB数组？

假设你的JSONB数据存储在表中（或直接使用字面量），可以通过PostgreSQL的JSON函数组合实现嵌套数组扁平化：

直接处理字面量JSONB #

如果是直接处理给定的JSONB字面量，执行以下SQL：

SELECT jsonb_agg(obj) AS flattened_json
FROM jsonb_array_elements('[
    [
        {"id": 51003,"user_id": 4554,"sg_id": 143353},
        {"id": 51004,"user_id": 4555,"sg_id": 143353}
    ],
    [
        {"id": 51005,"user_id": 4555,"sg_id": 143354}
    ]
]'::jsonb) AS inner_arr,
     jsonb_array_elements(inner_arr) AS obj;

处理表中的JSONB字段 #

如果数据存储在表（例如test_table）的jsonb_column字段中，使用以下SQL：

SELECT jsonb_agg(obj) AS flattened_json
FROM test_table,
     jsonb_array_elements(jsonb_column) AS inner_arr,
     jsonb_array_elements(inner_arr) AS obj;

保留原表其他字段的情况 #

如果需要保留表中的其他字段（例如主键id），添加GROUP BY即可：

SELECT 
    id,
    jsonb_agg(obj) AS flattened_json
FROM test_table,
     jsonb_array_elements(jsonb_column) AS inner_arr,
     jsonb_array_elements(inner_arr) AS obj
GROUP BY id;

原理说明 #

• jsonb_array_elements：用于展开JSON数组，第一次展开外层数组得到每个内层数组；第二次展开内层数组得到单个对象元素。
• jsonb_agg：将所有单个对象聚合为一个新的JSONB数组，即最终的扁平化结果。

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