带空值的OPENJSON交叉应用(TSQL)

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:

我有一系列的OPENJSON语句，在存储过程的最后一步，我从倒数第二个表的最后一列解析了一些JSON。最后一列几乎总是空的，但它是由JSON对象填充的，因此偶尔会有一些信息。我正在尝试将表1转换为FinalTable，如下所示:

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 [] because there is no data in the original JSON script for that object.

在StageStep2中，[调整]几乎总是包含[]，因为该对象的原始JSON脚本中没有数据。

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"

因为我在最后一列中没有数据，所以OPENJSON没有解析任何内容并且总是返回'0行受影响'基本上它只是在说"不，老兄，这里没有数据所以我没有解析shib"

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

如果列是空的，我怎么做才能使它仍然包含空值?

1 个解决方案

#1

Using OUTER APPLY instead of CROSS APPLY solved the issue

用外部应用代替交叉应用解决了这个问题