PostgreSQL如何按索引合并两个jsonb数组为键值JSON对象

最简方案（适用PostgreSQL 9.5及以上版本） #

直接使用内置的jsonb_object函数，该函数支持直接传入keys数组和values数组生成目标JSON对象，写法如下：

SELECT 
  jsonb_object(
    ARRAY(SELECT jsonb_array_elements_text(keys)),
    ARRAY(SELECT jsonb_array_elements_text(values))
  ) AS merged_json
FROM 你的表名;

方案说明 #

• jsonb_array_elements_text 用于将jsonb类型的数组展开为文本行
• 外层ARRAY()将展开的文本行重新组装为text数组，传入jsonb_object即可按索引对应生成键值对
• 无需提前知道数组长度，同一行keys和values长度一致即可正常运行

灵活聚合方案（适用于所有支持jsonb的PostgreSQL版本，支持自定义处理逻辑） #

如果需要在合并前对key/value做过滤、修改等操作，可以用jsonb_object_agg按索引对齐聚合的方式实现：

SELECT 
  jsonb_object_agg(k, v) AS merged_json
FROM 你的表名,
     jsonb_array_elements_text(keys) WITH ORDINALITY AS k_arr(k, idx),
     jsonb_array_elements_text(values) WITH ORDINALITY AS v_arr(v, idx)
WHERE k_arr.idx = v_arr.idx
GROUP BY 你的表名.主键列; -- 替换为你表中每行的唯一标识列，保证每行单独合并

方案说明 #

• WITH ORDINALITY 会在展开数组时带出元素的索引位置，通过索引对齐保证key和value的对应关系
• 可以在WHERE子句中增加过滤条件，或者在聚合前修改k、v的值，适配自定义需求

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