PostgreSQL多表关联查询：如何嵌套返回地点与关联国家数据

我有一个包含jobs、locations和countries的PostgreSQL数据库，表间关系如下：

• jobs与locations为多对多关系（通过中间表jobs_locations）
• jobs与countries为多对多关系（通过中间表jobs_countries）
• locations与countries为一对多关系

一个职位可通过两条路径关联到国家：jobs<=>jobs_locations<=>locations<=>countries、jobs<=>jobs_countries<=>countries。

当前查询语句：

select
  jobs.*,
  COALESCE(json_agg(locations) filter (where locations.id IS NOT NULL), '[]') as locations,
  COALESCE(json_agg(countries) filter (where countries.id IS NOT NULL), '[]') as countries
from jobs
left join jobs_locations on jobs.id = jobs_locations.job_id
left join locations on jobs_locations.location_id = locations.id
left join countries loc_country on locations.country_id = loc_country.id
left join jobs_countries on jobs.id = jobs_countries.job_id
left join countries on jobs_countries.country_id = countries.id
where loc_country.id = 2 OR countries.id = 2
group by jobs.id;

当前返回的数据格式：

{
  id: 1,
  ...
  locations: [
    {
      id: 10,
      name: "Paris",
      country_id: 100
    }
  ]
}

期望得到的数据格式：

{
  id: 1,
  ...
  locations: [
    {
      id: 10,
      name: "Paris",
      country: {
        id: 100,
        name: "France"
      }
    }
  ]
}

问题分析 #

原查询存在两个核心问题：

1. 直接聚合locations表的整行数据，未将关联的countries信息嵌套到location对象中；
2. 同时连接jobs_locations和jobs_countries会产生笛卡尔积，导致同一个location或country被多次聚合，结果可能出现重复数据。

修改后的查询语句 #

方案1：构造嵌套对象并处理笛卡尔积 #

SELECT
  jobs.*,
  -- 构造包含嵌套country的location对象并聚合
  COALESCE(
    json_agg(
      DISTINCT json_build_object(
        'id', locations.id,
        'name', locations.name,
        'country', json_build_object(
          'id', loc_country.id,
          'name', loc_country.name
        )
      )
      FILTER (WHERE locations.id IS NOT NULL)
    ), '[]'
  ) AS locations,
  -- 聚合关联的国家并去重
  COALESCE(
    json_agg(DISTINCT countries)
    FILTER (WHERE countries.id IS NOT NULL), '[]'
  ) AS countries
FROM jobs
LEFT JOIN jobs_locations ON jobs.id = jobs_locations.job_id
LEFT JOIN locations ON jobs_locations.location_id = locations.id
LEFT JOIN countries loc_country ON locations.country_id = loc_country.id
LEFT JOIN jobs_countries ON jobs.id = jobs_countries.job_id
LEFT JOIN countries ON jobs_countries.country_id = countries.id
WHERE loc_country.id = 2 OR countries.id = 2
GROUP BY jobs.id;

方案2：使用LATERAL JOIN避免笛卡尔积（推荐） #

这种写法通过LATERAL JOIN分别处理每个职位的locations和countries，从根源避免笛卡尔积，结果更准确且性能更优：

SELECT
  jobs.*,
  COALESCE(locs.locations_json, '[]') AS locations,
  COALESCE(cntrs.countries_json, '[]') AS countries
FROM jobs
-- 单独处理每个职位的关联locations及对应country
LEFT JOIN LATERAL (
  SELECT json_agg(
    json_build_object(
      'id', l.id,
      'name', l.name,
      'country', json_build_object(
        'id', c.id,
        'name', c.name
      )
    )
  ) AS locations_json
  FROM jobs_locations jl
  JOIN locations l ON jl.location_id = l.id
  JOIN countries c ON l.country_id = c.id
  WHERE jl.job_id = jobs.id
    AND c.id = 2
) locs ON true
-- 单独处理每个职位的直接关联countries
LEFT JOIN LATERAL (
  SELECT json_agg(DISTINCT c) AS countries_json
  FROM jobs_countries jc
  JOIN countries c ON jc.country_id = c.id
  WHERE jc.job_id = jobs.id
    AND c.id = 2
) cntrs ON true
-- 过滤出符合条件的职位
WHERE locs.locations_json IS NOT NULL OR cntrs.countries_json IS NOT NULL;

关键修改点说明 #

1. 嵌套对象构造：使用json_build_object将location的字段与对应的country对象组合，生成期望的嵌套结构；
2. 去重处理：使用DISTINCT关键字避免多表连接产生的重复数据被聚合；
3. LATERAL JOIN优化：通过横向连接分别处理每个职位的关联数据，彻底避免笛卡尔积问题，同时让查询逻辑更清晰。

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