[SQL] Using multiple Cross Joins with JSON seems to slow my query right down and never...

I have the following JSON:

[
{
"UserDetails": [
{
"UserName": "User1",
"UserDateOfBirth": "06/11/89",
"UserID": "12345",
"NotesDay1": [
{
"NoteID": "a287fcc4-4b3e-4b63-ac61-dc063c53894d",
"AnswerType": 1,
"RemedialText": null,
"Details": null,
"UserLocation": 0,
"DateDone": "2021-08-06T00:19:14.673+00:00"
},
{
"NoteID": "4a48385a-f73a-44ff-bde0-a7de6aa3d965",
"AnswerType": 1,
"RemedialText": null,
"Details": null,
"UserLocation": 0,
"DateDone": "2021-08-06T02:19:59.373+00:00"
},
{
"NoteID": "ddb6bc52-634f-4960-b753-619b18ecd1fa",
"AnswerType": 1,
"RemedialText": null,
"Details": null,
"UserLocation": 0,
"DateDone": "2021-08-06T04:14:50.214+00:00"
}
]
},
{
"UserName": "User2",
"UserDateOfBirth": "05/08/99",
"UserID": "23456",
"NotesDay1": [],
"NotesDay2": [
{
"NoteID": "62cf5478-0705-4cb5-8a18-97c5335de606",
"AnswerType": 1,
"RemedialText": null,
"Details": null,
"UserLocation": 0,
"DateDone": "2021-08-07T01:00:48.071+00:00"
},
{
"NoteID": "7f864ef4-4624-4b2e-a40e-bfa77a59e0b0",
"AnswerType": 1,
"RemedialText": null,
"Details": null,
"UserLocation": 0,
"DateDone": "2021-08-07T01:00:48.071+00:00"
},
{
"NoteID": "db1a0af0-6274-4237-a379-77c4c54f2973",
"AnswerType": 1,
"RemedialText": null,
"Details": null,
"UserLocation": 0,
"DateDone": "2021-08-07T06:28:02.962+00:00"
},
{
"NoteID": "2ae6b923-e852-4d0b-96e3-194c58eec28f",
"AnswerType": 1,
"RemedialText": null,
"Details": null,
"UserLocation": 0,
"DateDone": "2021-08-07T06:28:02.962+00:00"
}
],
"NotesDay3": [],
"NotesDay4": [],
"NotesDay5": [],
"NotesDay6": [],
"NotesDay7": []
}
]
}
]


My current SQL query to import this is set up as below:

SELECT j2.UserID, j3.NoteID1, j4.NoteID2, j5.NoteID3, j6.NoteID4, j7.NoteID5, j8.NoteID6, j9.NoteID7
INTO [UserDayNotes]
FROM OPENJSON(@JSON)
WITH
(
UserDetails nvarchar(max) '$.UserDetails' as JSON
) j1

CROSS APPLY OPENJSON(j1.UserDetails) WITH
(
UserID nvarchar(100) '$.UserID',
NotesDay1 nvarchar(max) '$.NotesDay1' as JSON,
NotesDay2 nvarchar(max) '$.NotesDay2' as JSON,
NotesDay3 nvarchar(max) '$.NotesDay3' as JSON,
NotesDay4 nvarchar(max) '$.NotesDay4' as JSON,
NotesDay5 nvarchar(max) '$.NotesDay5' as JSON,
NotesDay6 nvarchar(max) '$.NotesDay6' as JSON,
NotesDay7 nvarchar(max) '$.NotesDay7' as JSON
) j2

CROSS APPLY OPENJSON(j2.NotesDay1) WITH
(
NoteID1 nvarchar(100) '$.NoteID'
) j3

CROSS APPLY OPENJSON(j2.CareNotesDay2) WITH
(
NoteID2 nvarchar(100) '$.NoteID'
) j4

CROSS APPLY OPENJSON(j2.CareNotesDay3) WITH
(
NoteID3 nvarchar(100) '$.NoteID'
) j5

CROSS APPLY OPENJSON(j2.CareNotesDay4) WITH
(
NoteID4 nvarchar(100) '$.NoteID'
) j6

CROSS APPLY OPENJSON(j2.CareNotesDay5) WITH
(
NoteID5 nvarchar(100) '$.NoteID'
) j7

CROSS APPLY OPENJSON(j2.CareNotesDay6) WITH
(
NoteID6 nvarchar(100) '$.NoteID'
) j8

CROSS APPLY OPENJSON(j2.CareNotesDay7) WITH
(
NoteID3 nvarchar(100) '$.NoteID'
) j9


If I run the query as it is, then it runs forever and ever and never completes. If I comment out all but j2.UserID, j3.NoteID1 and j4.NoteID2, then the query runs more or less instantly. I'm assuming this is something to do with the number of cross apply items I have, but my experience and knowledge of this side of SQL is not great, and I could really use some help.

How do I get to the point where I have the following data layout:

UserID NotesDay1 NotesDay2 NotesDay3 NotesDay4 NotesDay 5 NotesDay 6 NotesDay7
User1 12345 23456 34567 45678 56789
User2 54321 65432 76543 87654

(Good lord, that table took some doing!)

Thanks in advance.

Continue reading...