Brad Mathews
Brad Mathews

Reputation: 1857

JSON AUTO in SQL Server Query is Duplicating Records in Parent Array

I am using FOR JSON AUTO on Azure SQL to return my query in JSON and the JSON AUTO part is not quite working fully. I have multiple subtables in my query and SQL Server is returning a duplicate subtable record and I can't figure out why.

job_id 6 has 3 matching tasks records. But two are showing up in the first item in the results array and on is in the last item in the result array.

Here is the query:

select (
    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.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.contractor_id,
      contractors.name
    from jobs as jobs
    join tasks as tasks on tasks.job_id = jobs.job_id
    left join contractor_task as contractor_task on tasks.task_id = contractor_task.task_id
    left join contractors as contractors on contractors.contractor_id = contractor_task.contractor_id
    where project_id = 6
    FOR JSON AUTO, INCLUDE_NULL_VALUES
) as json

Here is a subset of the results showing the duplicate record:

    [
     {
      "project_id": 6,
      "job_name": "Retool  Show Filter Button Bug.mp4",
      "job_id": 6,
      "job_location": "Classroom",
      "tasks": [
       {
        "task_name": "Transcribe Video 123",
        "task_location": "",
        "task_start": "2022-09-02T01:30:00",
        "contractors": [
         {
          "contractor_id": 1,
          "name": "Brad Mathews"
         },{
          "contractor_id": 2,
          "name": "Fred Mathews"
         }],
        "task_end": null,
        "assignments_filled": false,
        "task_notes": "",
        "task_type_id": 3,
        "task_id": 12,
        "caption_tools_id": null
       },{
        "task_name": "Post Video Transcription Processing",
        "task_location": null,
        "task_start": null,
        "contractors": [
         {
          "contractor_id": 1,
          "name": "Brad Mathews"
         }],
        "task_end": null,
        "assignments_filled": false,
        "task_notes": null,
        "task_type_id": 4,
        "task_id": 13,
        "caption_tools_id": null
       }],
      "job_start": "2022-09-23T00:45:00",
      "job_end": "2022-09-22T00:45:00",
      "job_due": "2022-09-15T00:00:00"
     },
     ......
     ,{
      "project_id": 6,
      "job_name": "Retool  Show Filter Button Bug.mp4",
      "job_id": 6,
      "job_location": "Classroom",
      "tasks": [
       {
        "task_name": "test Task",
        "task_location": "Home",
        "task_start": "2022-09-15T12:15:00",
        "contractors": [
         {
          "contractor_id": null,
          "name": null
         }],
        "task_end": null,
        "assignments_filled": false,
        "task_notes": "Notes Here",
        "task_type_id": 1,
        "task_id": 102,
        "caption_tools_id": null
       }],
      "job_start": "2022-09-23T00:45:00",
      "job_end": "2022-09-22T00:45:00",
      "job_due": "2022-09-15T00:00:00"
     }]

Upvotes: 0

Views: 70

Answers (0)

Related Questions