PostgreSQL 如何动态将key/value结构jsonb数组转为普通jsonb对象
PostgreSQL 动态jsonb数组转对象的实现方案
核心实现逻辑
jsonb_build_object的动态键限制,可以通过PostgreSQL内置聚合函数jsonb_object_agg突破。该函数原生支持将成对收集的键、值动态聚合为jsonb对象,全程不需要提前预知具体键名,完全适配键值动态变化的业务场景。
具体实现写法
1. 行内转换、保留原表全字段的写法
不需要对原表额外分组,直接在子查询中完成数组展开与聚合,可关联原表所有字段直接返回结果:
SELECT topi.*, ( SELECT jsonb_object_agg(elem ->> 'key', elem -> 'value') FROM jsonb_array_elements(topi.other_information) AS elem ) AS converted_other_information FROM items_data.tbl_items topi;
elem ->> 'key'提取数组每个元素的key值,作为输出jsonb对象的属性名elem -> 'value'保留原value的jsonb格式,避免丢失数字、布尔、嵌套对象等类型信息- 该写法不需要提前声明所有键名,会自动适配数组中出现的任意键值对
2. 大表场景的高效实现
对于数据量较大的表,使用LATERAL连接展开数组的性能更优,配合jsonb_object_agg聚合完成转换:
SELECT topi.item_id, -- 替换为原表的实际主键字段 jsonb_object_agg( elem ->> 'key', elem -> 'value' ) AS converted_other_information FROM items_data.tbl_items topi CROSS JOIN LATERAL jsonb_array_elements(topi.other_information) AS elem GROUP BY topi.item_id;
- 相比在SELECT中两次调用
jsonb_array_elements的写法,LATERAL连接只会展开一次数组,避免产生笛卡尔积导致的重复数据 - 当数组中存在重复key值时,
jsonb_object_agg默认会用最后出现的元素值覆盖前面的同key值,如需调整覆盖优先级,可在函数内通过ORDER BY指定排序规则。
异常场景处理
如果数组元素存在空key的情况,可在数组展开逻辑后添加WHERE elem ->> 'key' IS NOT NULL过滤无效元素,避免聚合报错。
内容的提问来源于stack exchange,提问作者sharkguto
相关产品推荐
相关产品推荐

