PostgreSQL中jsonb数组提取指定字段转为JSON数组的问题

你的SQL存在两个问题：

• 字段名拼写错误：把translation写成了translations
• 仅用jsonb_array_elements会将数组元素拆分为单行记录，无法直接生成目标格式的JSON数组

以下两种方法可以实现需求：

方法一：使用jsonb_array_elements + jsonb_agg #

通过横向展开数组元素，提取指定值后再聚合为JSON数组：

SELECT jsonb_agg(tag -> 'translation' ->> 'en_US') AS en_us_translations
FROM tags,
     jsonb_array_elements(product_data -> 'tags') AS tag;

• jsonb_array_elements(product_data -> 'tags') 将tags数组拆分为单独的行记录
• tag -> 'translation' ->> 'en_US' 提取每个标签对象中translation下的en_US文本值
• jsonb_agg() 将所有提取的值聚合为一个JSON数组

方法二：使用jsonb_path_query_array（更简洁） #

通过JSONPath表达式直接提取并生成目标数组：

SELECT jsonb_path_query_array(product_data, '$.tags[*].translation.en_US') AS en_us_translations
FROM tags;

• $.tags[*] 匹配tags数组中的所有元素
• .translation.en_US 定位到每个元素下的目标字段
• jsonb_path_query_array 自动将所有匹配值组合为JSON数组

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