PostgreSQL 11中jsonb_path_query_array的等效替代方案

PostgreSQL 11 未引入jsonpath系列函数，可通过「展开JSON数组→提取目标字段→重新聚合为数组」的逻辑实现等效效果，具体实现如下：

方案1：返回JSONB数组（和原jsonb_path_query_array输出格式完全一致） #

SELECT 
  COALESCE(jsonb_agg(elem -> 'key'), '[]'::jsonb) AS keys
FROM your_table
-- 用LEFT JOIN兼容原数组为空的场景，避免返回NULL
LEFT JOIN jsonb_array_elements(your_jsonb_column) AS elem ON true
-- 按表的主键/唯一标识分组，保证每行结果和原行一一对应
GROUP BY your_table.id;

方案2：返回文本类型数组 #

如果需要直接得到字符串数组而非JSONB数组，可调整为：

SELECT 
  COALESCE(array_agg(elem ->> 'key'), ARRAY[]::text[]) AS keys
FROM your_table
LEFT JOIN jsonb_array_elements(your_jsonb_column) AS elem ON true
GROUP BY your_table.id;

逻辑说明 #

• jsonb_array_elements 函数负责将JSONB类型的数组展开为多行，每行对应数组中的一个元素
• -> 操作符提取元素中key字段的JSONB类型值，->> 则将值转换为文本字符串
• jsonb_agg/array_agg 负责将提取的字段值重新聚合为数组，搭配COALESCE处理空数组场景，和原函数行为完全对齐
• 如果仅查询单条记录，可省略GROUP BY子句

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