PostgreSQL：将JSON数组转换为按ID索引的单个JSON

在PostgreSQL中，你可以通过拆分JSONB数组+聚合函数的方式实现这个转换，核心用到jsonb_array_elements（拆分数组元素）和jsonb_object_agg（聚合生成键值对对象）。

直接查询示例 #

假设你有一个JSONB数组变量或字段，执行以下SQL即可得到目标结构：

SELECT jsonb_object_agg((elem->>'id')::text, elem)
FROM jsonb_array_elements('[{"id": 1, "name": "John"}, {"id": 2, "name": "Jane"}]'::jsonb) AS elem;

封装为可复用的SQL函数 #

如果要在自定义SQL函数中复用该逻辑，可以写成：

CREATE OR REPLACE FUNCTION array_to_id_keyed_jsonb(arr jsonb)
RETURNS jsonb AS $$
BEGIN
  RETURN (
    SELECT jsonb_object_agg((elem->>'id')::text, elem)
    FROM jsonb_array_elements(arr) AS elem
  );
END;
$$ LANGUAGE plpgsql;

调用函数时只需传入目标数组：

SELECT array_to_id_keyed_jsonb('[{"id":1,"name":"John"},{"id":2,"name":"Jane"}]'::jsonb);

注意事项 #

• 如果数组中存在重复ID，jsonb_object_agg会自动保留最后一个出现的元素（后出现的覆盖先出现的）。
• 确保数组中每个元素都包含id字段，否则会出现键为null的条目；若需过滤无id的元素，可在查询中添加WHERE elem ? 'id'条件。

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