Postgres如何通过原生方法实现JSONB数组按指定字段keyBy功能

实现方案 #

1. 单查询直接实现 #

直接用Postgres原生的jsonb_array_elements拆分数组，配合jsonb_object_agg聚合即可，和lodash的_.keyBy逻辑完全一致：

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

运行上述查询返回的结果就是你需要的按id索引的JSONB对象。

注：JSON规范要求对象键必须为字符串，因此输出的键会是字符串格式（如"1"），但Postgres支持直接用数值类型索引取值，不影响实际使用。

2. 封装为可复用函数 #

如果需要频繁使用，可以封装为自定义SQL函数，调用方式和lodash的_.keyBy完全一致：

-- 创建函数
CREATE OR REPLACE FUNCTION jsonb_key_by(arr jsonb, key_field text)
RETURNS jsonb AS $$
SELECT jsonb_object_agg(elem ->> key_field, elem)
FROM jsonb_array_elements(arr) AS elem;
$$ LANGUAGE sql STABLE;

-- 调用示例
SELECT jsonb_key_by(你的jsonb数组字段, 'id') FROM 你的表名;

特性说明 #

• 逻辑和lodash的_.keyBy完全对齐：如果数组中存在指定key字段值重复的元素，后出现的元素会覆盖先出现的元素
• 如果数组元素不存在指定的key字段，对应键值会为null，聚合后null键对应的元素为最后一个无对应key的元素

内容的提问来源于stack exchange，提问作者Dominic Bou-Samra