如何使用PostgreSQL的JSON聚合函数通过单连接查询处理一对多关系的三张表

你完全可以通过JOIN结合PostgreSQL原生的JSON聚合函数来替代多层子查询，写出更简洁易读且性能更优的语句。下面是具体的实现方案：

最终查询语句 #

SELECT
  json_agg(
    json_build_object(
      'country_name', c.name,
      'leader', c.leader,
      'states', s.states_data
    )
  ) AS result
FROM countries c
JOIN (
  SELECT
    country_id,
    json_agg(
      json_build_object(
        'state_name', s.name,
        'population', s.population,
        'cities', ci.cities_data
      )
    ) AS states_data
  FROM states s
  JOIN (
    SELECT
      state_id,
      json_agg(
        json_build_object('city_name', ci.name)
      ) AS cities_data
    FROM cities ci
    GROUP BY state_id
  ) ci ON s.id = ci.state_id
  WHERE s.population IN (39500000, 125700000)
  GROUP BY country_id
) s ON c.id = s.country_id;

语句拆解说明 #

1. 最内层（城市聚合）：
   先按state_id对城市分组，用json_build_object()构造单个城市的JSON结构，再通过json_agg()把同一州的城市打包成数组，得到每个州对应的城市集合。

2. 中间层（州聚合）：
   将筛选出指定人口的州表，和聚合后的城市数据做连接，同样用json_build_object()构造州的JSON结构，再按country_id分组聚合，得到每个国家对应的州集合。

3. 最外层（国家聚合）：
   连接国家表和聚合后的州数据，构造包含国家信息的JSON对象，最后用json_agg()把所有国家打包成最终的JSON数组。

查询结果 #

执行后会直接输出你需要的嵌套格式：

[
  {
    "country_name": "USA",
    "leader": "Joe Biden",
    "states": [
      {
        "state_name": "California",
        "population": 39500000,
        "cities": [
          {
            "city_name": "San Francisco"
          }
        ]
      }
    ]
  },
  {
    "country_name": "India",
    "leader": "Narendra Modi",
    "states": [
      {
        "state_name": "Maharastra",
        "population": 125700000,
        "cities": [
          {
            "city_name": "Mumbai"
          },
          {
            "city_name": "Pune"
          }
        ]
      }
    ]
  }
]

额外优化提示 #

如果存在没有对应城市的州，你可以把语句中的JOIN替换成LEFT JOIN，这样即使州下没有城市，也会返回空数组[]，更贴合实际业务场景。

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