OPENJSON cross apply with NULL values (TSQL)

Question

I have a series of OPENJSON statements and on the final step of my stored procedure, I parse some JSON from the final column in the second-to-last table. The last column is almost always empty, but it is populated from a JSON object so it will occasionally have some information. I am trying to convert Table 1 into FinalTable as shown here:

Table 1
Col1  Col2  Col3  Col4  Col5  Col6  Col7  Adjustments
123   592   593   data  rand  fake  data  []
345   035   021   ll    need  food  now   [ { "id": 999, "adj1": 123 }]

FinalTable
Col1  Col2  Col3  Col4  Col5  Col6  Col7  AdjID  Adj1  Adj2  Adj3
123   592   593   data  rand  fake  data  NULL   NULL  NULL  NULL
345   035   021   ll    need  food  now   999    123   NULL  NULL

Here is my code:

INSERT into FinalTable ([Col1], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [AdjID], [Adj1], [Adj2], [Adj3]
) 
SELECT [Col1], [Col2], [Col3], [Col4], [Col5], [Col6], [Col7], [AdjID], [Adj1], [Adj2], [Adj3]

FROM StageStep2 cross apply
OPENJSON (Adjustments)
WITH (
         AdjID         nvarchar(200)     '$.id',
         [Adj1]        nvarchar(200)     '$.adj1',
         [Adj2]        nvarchar(200)     '$.adj2',
         [Adj3]        nvarchar(200)     '$.adj3')

In StageStep2, [Adjustments] almost always contains empty arrays shown as [] because there is no data in the original JSON script for that object.

It would appear that because I have no data in the final column, OPENJSON is not parsing anything and always returning '0 rows affected' Basically it's just saying: "nah dude there's no data here so I ain't parsing shib"

What can I do to make it still contain NULL values if the column is empty?

Hensley

Answer 1

Using OUTER APPLY instead of CROSS APPLY solved the issue

Hensley