PostgreSQL：无需unnest提取jsonb对象数组指定键值为数组

问题场景 #

需要将jsonb类型的对象数组转换为仅包含指定键对应值的数组，例如从包含{"a": x, "b": y}结构的对象数组中提取所有a键的值组成新数组，且不使用unnest函数。

测试数据 #

SELECT t.column1 AS data FROM (VALUES
        ('[{"a": 1, "b": 5 },{"a": 2, "b": 6 },{"a": 3, "b": 7 },{"a": 4, "b": 8 }]'::jsonb),
        ('[{"a": 2, "b": 5 },{"a": 4, "b": 6 },{"a": 5, "b": 7 },{"a": 6, "b": 8 }]'::jsonb),
        ('[{"a": 7, "b": 5 },{"a": 8, "b": 6 },{"a": 9, "b": 7 },{"a": 10, "b": 8 }]'::jsonb)
    ) t;

期望结果 #

data
---------------------
[1,2,3,4]
[2,4,5,6]
[7,8,9,10]

解决方案 #

使用PostgreSQL 12及以上版本支持的jsonb_path_query_array函数，该函数可通过JSON路径表达式直接提取数组元素的指定键值并重组为数组，无需展开原数组。

查询语句

SELECT jsonb_path_query_array(t.column1, '$[*].a') AS data
FROM (VALUES
        ('[{"a": 1, "b": 5 },{"a": 2, "b": 6 },{"a": 3, "b": 7 },{"a": 4, "b": 8 }]'::jsonb),
        ('[{"a": 2, "b": 5 },{"a": 4, "b": 6 },{"a": 5, "b": 7 },{"a": 6, "b": 8 }]'::jsonb),
        ('[{"a": 7, "b": 5 },{"a": 8, "b": 6 },{"a": 9, "b": 7 },{"a": 10, "b": 8 }]'::jsonb)
    ) t;

说明 #

• $[*].a是JSON路径表达式：$代表当前JSON对象，[*]遍历数组中所有元素，.a提取每个元素的a键对应值，函数自动将这些值组合为新的jsonb数组。
• 该方法无需依赖unnest或数组展开函数，操作更简洁高效。

若使用PostgreSQL 11及以下版本（无jsonb_path_query_array），可通过jsonb_agg结合jsonb_array_elements实现，但本质仍涉及数组展开；如果必须完全规避展开类函数，可考虑自定义PL/pgSQL函数处理，但推荐优先升级版本使用原生路径函数。

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