Reputation: 1857
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