PostgreSQL中如何提取JSON数组各元素指定字段（不修改子查询）

你可以在主查询的SELECT子句中，通过JSON数组拆解+重新聚合的方式，在不修改关联子查询的前提下，将标签对象数组转换为仅包含name字段的数组，具体实现如下：

SELECT 
  -- 将标签对象数组转换为name字段数组，无标签时返回空数组
  COALESCE(
    (SELECT json_agg(tag->>'name') FROM json_array_elements(associated_tags.tags) AS tag),
    '[]'::json
  ) AS tags,
  stores.* 
FROM "stores" 
LEFT JOIN ( 
  SELECT taggings.taggable_id as store_id, JSON_AGG(tags.*) as tags
  FROM taggings
  INNER JOIN (SELECT * FROM tags ORDER BY name) AS tags ON tags.id=taggings.tag_id
  WHERE taggings.taggable_type='Store' AND taggings.context='tags'
  GROUP BY taggings.taggable_id
) associated_tags ON associated_tags.store_id=stores.id 
ORDER BY (associated_tags.tags->0->>'name') desc NULLS LAST, (stores.id) asc NULLS FIRST

关键逻辑说明 #

• json_array_elements(associated_tags.tags)：把原查询返回的标签对象JSON数组拆分成单个的JSON对象行
• tag->>'name'：从每个标签对象中提取name字段的文本值
• json_agg(...)：将提取出的所有name值重新聚合成一个新的JSON数组
• COALESCE(..., '[]'::json)：处理没有关联标签的情况（LEFT JOIN导致的NULL），将其替换为空数组而非NULL

适配JSONB类型的版本 #

如果你的字段是jsonb类型（PostgreSQL推荐使用的JSON类型），只需替换对应的函数：

SELECT 
  COALESCE(
    (SELECT jsonb_agg(tag->>'name') FROM jsonb_array_elements(associated_tags.tags) AS tag),
    '[]'::jsonb
  ) AS tags,
  stores.* 
FROM "stores" 
LEFT JOIN ( 
  SELECT taggings.taggable_id as store_id, JSONB_AGG(tags.*) as tags
  FROM taggings
  INNER JOIN (SELECT * FROM tags ORDER BY name) AS tags ON tags.id=taggings.tag_id
  WHERE taggings.taggable_type='Store' AND taggings.context='tags'
  GROUP BY taggings.taggable_id
) associated_tags ON associated_tags.store_id=stores.id 
ORDER BY (associated_tags.tags->0->>'name') desc NULLS LAST, (stores.id) asc NULLS FIRST

注意：原排序逻辑依赖子查询中已按name排序的标签数组，所以转换后的name数组顺序会和原标签对象数组的顺序保持一致，排序条件无需修改。

内容的提问来源于stack exchange，提问作者Ryan Pierce Williams