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.