PostgreSQL中动态将JSON数组转换为JSON对象的方法求助

要实现未知键的JSON数组转扁平化JSON对象，可通过拆分数组、展开键值对再聚合的方式完成，以下是具体实现：

基础示例（直接处理字面量） #

如果输入是JSON数组字面量，执行以下SQL即可得到目标结果：

SELECT json_object_agg(key, value) AS flattened_json
FROM jsonb_array_elements('[{"col1": "1", "col2": null, "col3": "7"}, {"col4": "19"}, {"col5": "19", "col6":"18"}]'::jsonb) AS arr_items,
     jsonb_each(arr_items) AS kv_pairs;

表字段处理示例 #

如果JSON数组存储在表的字段中（假设表名为data_table，字段名为json_arr_col），使用以下SQL：

SELECT json_object_agg(kv.key, kv.value) AS flattened_json
FROM data_table,
     jsonb_array_elements(data_table.json_arr_col) AS arr_items,
     jsonb_each(arr_items) AS kv;

关键步骤说明 #

• jsonb_array_elements：将输入的JSON数组拆分为单独的JSON对象行
• jsonb_each：将每个JSON对象拆解为key（键）和value（值）的行记录
• json_object_agg：将所有键值对聚合为单个JSON对象，若存在重复键，后出现的键值对会覆盖先出现的

注：如果使用json类型而非jsonb，只需将上述SQL中的jsonb_array_elements替换为json_array_elements，jsonb_each替换为json_each即可。

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