Azure SQL中FOR JSON AUTO生成JSON时父数组记录重复问题

问题原因 #

你遇到的重复父job记录问题，根源是单一层JOIN查询产生的笛卡尔积，加上FOR JSON AUTO自动分组逻辑的局限性。当任务关联多个承包商，或者某个任务无关联承包商时，JOIN后的结果行会出现同一job对应多行数据，FOR JSON AUTO无法识别这些行属于同一个父job，进而拆分成多个重复的job对象。

解决方案 #

通过嵌套子查询明确JSON层级结构，分别处理job、task、contractor的层级关系，避免JOIN导致的行重复干扰分组逻辑。修改后的查询语句如下：

SELECT
  jobs.project_id,
  jobs.job_id,
  jobs.job_name,
  jobs.date_time_due AS job_due,
  jobs.date_time_start AS job_start,
  jobs.date_time_end AS job_end,
  jobs.location AS job_location,
  -- 嵌套子查询生成tasks数组
  (
    SELECT
      tasks.task_id,
      tasks.task_name,
      tasks.date_time_start AS task_start,
      tasks.date_time_end AS task_end,
      tasks.assignments_filled,
      tasks.notes AS task_notes,
      tasks.location AS task_location,
      tasks.task_type_id,
      tasks.caption_tools_id,
      -- 嵌套子查询生成contractors数组
      (
        SELECT
          contractors.contractor_id,
          contractors.name
        FROM contractor_task
        JOIN contractors ON contractors.contractor_id = contractor_task.contractor_id
        WHERE contractor_task.task_id = tasks.task_id
        FOR JSON AUTO, INCLUDE_NULL_VALUES
      ) AS contractors
    FROM tasks
    WHERE tasks.job_id = jobs.job_id
    FOR JSON AUTO, INCLUDE_NULL_VALUES
  ) AS tasks
FROM jobs
WHERE jobs.project_id = 6
FOR JSON AUTO, INCLUDE_NULL_VALUES

修改说明 #

1. 外层查询仅处理job的核心字段，通过子查询嵌套生成tasks数组，确保每个job只生成一个父对象
2. tasks数组的子查询中，再嵌套生成contractors数组，直接通过task_id关联获取对应承包商数据，避免主查询JOIN产生的笛卡尔积
3. 这种明确的层级结构强制了job→tasks→contractors的关系，确保同一job下的所有task都会被合并到同一个tasks数组中，不会因承包商关联情况拆分父job对象

修改后，job_id=6的3条任务记录会全部放在同一个job对象的tasks数组内，不会出现重复的父job记录。

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