How to parse nested JSON array using SQL Server
I just had the same problem, and I ended up solving it with multiple CROSS APPLY clauses.
Here's an example of my JSON:
DECLARE @PermsJSON NVARCHAR(MAX) =N'[{ "AppId": 1, "Perms": [{ "Permission": ["AA", "BB"], "PermissionTypeID": 2 }, { "Permission": ["10"], "PermissionTypeID": 1 }]},{ "AppId": 2, "Perms": [{ "Permission": ["IM", "NM"], "PermissionTypeID": 2 }, { "Permission": ["42"], "PermissionTypeID": 1 }]}]';
And then I can parse it using the following query:
SELECT a.AppId ,[Permission] = c.Value ,b.PermissionTypeIDFROM OPENJSON(@PermsJSON) WITH ( AppId INT N'$.AppId' ,Perms NVARCHAR(MAX) AS JSON ) AS aCROSS APPLY OPENJSON(a.Perms) WITH ( PermissionTypeID INT ,[Permission] NVARCHAR(MAX) AS JSON ) AS bCROSS APPLY OPENJSON(b.Permission) AS c;
The results then look like this:
AppId Permission PermissionTypeID1 AA 21 BB 21 10 12 IM 22 NM 22 42 1
After MUCH searching I finally discovered the answer to this issue. I simply have to include the Nested Array as another JSON column in my query, like:
WITH (DOC_ID varchar(100) '$.doc._id', DOC_REV varchar(45) '$.doc._rev', DELY_APPL_NAME varchar(20) '$.doc.delivery.application', DELY_SENT_BY varchar(25) '$.doc.delivery.sender.id', DELY_SENT_TYPO varchar(20) '$.doc.delivery.sender.type', RECIPS nvarchar(max) '$.doc.delivery.recipients' as JSON, PAYLOAD_START_TIME varchar(30) '$.doc.payload.startTS', .... ... .. ) as my_query
So, I end up with one record per JSON document, with (in this case) a varchar column containing JSON text.
Next, I can run a separate query on this column to parse the JSON and create a "child table" associated with the Parent.