PostgreSQL实现按城市分组的嵌套JSON结构聚合查询

核心实现语句 #

不需要复杂的CTE，直接通过GROUP BY结合PostgreSQL的JSON聚合函数就能实现需求，完整查询语句如下：

SELECT json_object_agg(
    city ORDER BY city ASC,  -- 按字母排序城市作为JSON的键
    json_agg(name ORDER BY name ASC)  -- 每个城市内的名字按字母排序并生成数组
) AS city_name_json
FROM my_table
GROUP BY city
-- 若需过滤无效数据，可添加WHERE条件，比如 WHERE city IS NOT NULL
;

语句说明 #

• json_agg(name ORDER BY name ASC)：对每个城市分组内的name字段按字母升序排序，再聚合为JSON数组，逻辑和你之前单个城市的查询一致，只是整合到分组逻辑中。
• json_object_agg(city ORDER BY city ASC, ...)：将每个分组的城市名作为JSON对象的键，对应的名字数组作为值，同时按字母升序排序所有城市键，最终生成符合要求的层级JSON结构。

输出结果示例 #

执行后会返回如下格式的JSON（与你给出的示例结构一致）：

{
  "LA": ["Michael"],
  "NYC": ["Allie", "John"],
  "Phoenix": ["Beatrice", "Dave"]
}

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