Home > Net >  Add a series of (known) nested arrays in a JSON extract into SQL
Add a series of (known) nested arrays in a JSON extract into SQL

Time:03-10

I have the following JSON as an example:

[{"UserDetails":[{"UserName":"Mr John Smith","UserDateOfBirth":"22/05/59","UserID":"ABC123","NotesDay1":[],"NotesDay2":[],"NotesDay3":[{"NoteID":"NI9199","Note":null}],"NotesDay4":[],"NotesDay5":[],"NotesDay6":[],"NotesDay7":[{"NoteID":"NI3423","Note":"Tried to contact, will try again later"}]}]}]

The format is that within the UserDetails array there are a number of users (just one in this example), and within each user object there are 7 arrays called NotesDay1 through to NotesDay7. Some users will have one or more objects within NotesDayX, whereas sometimes there are none.

The desired output is as follows:

USER         NoteID
ABC123       NI9199
ABC123       NI3243

etc

Each user for that 7 day period needs to be displayed in this way. Including any of my own code at this juncture seems to be pointless, as it presently doesn't work.

Can anyone help?

Thanks in advance.

CodePudding user response:

A possible approach is to use a variable as a value of path (SQL Server 2017 is required):

JSON:

DECLARE @json nvarchar(max) = N'
  [
     {"UserDetails": [
        {"UserName":"Mr John Smith","UserDateOfBirth":"22/05/59","UserID":"ABC123","NotesDay1":[],"NotesDay2":[],"NotesDay3":[{"NoteID":"NI9199","Note":null}],"NotesDay4":[],"NotesDay5":[],"NotesDay6":[],"NotesDay7":[{"NoteID":"NI3423","Note":"Tried to contact, will try again later"}]}, 
        {"UserName":"Mr John Smith 2","UserDateOfBirth":"22/05/59","UserID":"ABC124","NotesDay1":[],"NotesDay2":[],"NotesDay3":[{"NoteID":"NI9199","Note":null}],"NotesDay4":[],"NotesDay5":[],"NotesDay6":[],"NotesDay7":[{"NoteID":"NI3423","Note":"Tried to contact, will try again later"}]}]
     }
  ]'

Statemewnt:

SELECT JSON_VALUE(j.[value], '$.UserID') AS [UserId], a.NoteId
FROM OPENJSON(@json, '$[0].UserDetails') j
CROSS APPLY (VALUES (1), (2), (3), (4), (5), (6), (7) ) v (n)
CROSS APPLY OPENJSON(j.[value], CONCAT('$.NotesDay', v.n)) WITH (
   NoteId nvarchar(10) '$.NoteID'
) a

Result:

UserId NoteId
ABC123 NI9199
ABC124 NI9199
ABC123 NI3423
ABC124 NI3423

CodePudding user response:

We do the CROSS APPLY/OPENJSON dance to peel the onion:

SELECT UserID, NoteID
FROM OPENJSON(@json, '$[0].UserDetails')
WITH (
    UserID NVARCHAR(50),
    Details NVARCHAR(MAX) '$' AS JSON
)
CROSS APPLY (
    SELECT [value] AS [notes]
    FROM OPENJSON(Details)
    WHERE [key] LIKE 'NotesDay%'
) _
CROSS APPLY OPENJSON([notes])
WITH (
    NoteID NVARCHAR(50)
)

Note that this solution doesn't care how many days there are, and it works in SQL Server 2016.

  • Related