当路径正确时，OPENJSON()SQL SERVER将返回null

Question

这是我声明的json:

DECLARE @json VARCHAR(MAX) = N'
[
{
"mTruckId": -35839339,
"mPositionId": 68841545,
"mPositionDateGmt": "laboris ipsum ullamco",
"mLatitude": -36598160.205007434,
"mLongitude": 54707169.834195435,
"mGpsValid": false,
"mHeading": 114,
"mSpeed": -888256.4982997179,
"mAdditionalInformation": {
"mVin": "voluptate veniam",
"mOdometer": 25567959.615529776,
"mEngineHours": -87509827.08880372,
"mTemperatureSensors": [
{
"mUnit": "C",
"mLabel": "aute in",
"mValue": -74579140.64111689
},
{
"mUnit": "C",
"mLabel": "ullamco labore dolore",
"mValue": -91870052.84894001
}
]
}
},
{
"mTruckId": 80761376,
"mPositionId": 88380593,
"mPositionDateGmt": "sed pariatur ut sint",
"mLatitude": 62504812.42302373,
"mLongitude": 14622406.17103973,
"mGpsValid": false,
"mHeading": 302,
"mSpeed": 39030054.634676635,
"mAdditionalInformation": {
"mVin": "aute",
"mOdometer": 74400412.05641022,
"mEngineHours": 88453976.08453897,
"mTemperatureSensors": [
{
"mUnit": "F",
"mLabel": "reprehenderit consectetur id ipsum",
"mValue": 22634605.53841141
},
{
"mUnit": "C",
"mLabel": "magna consectetur esse",
"mValue": 72633803.44269562
}
]
}
}
]'

这是我从json中提取温度传感器数据的代码。我认为它会起作用，因为这个json中获取温度传感器数据的层次结构是root->m附加信息->m温度传感器。

SELECT  Unit,
Label,
Value
FROM OPENJSON(@json)
WITH(
Unit    VARCHAR(15) '$.mAdditionalInformation.mTemperatureSensors.mUnit',
Label   VARCHAR(50) '$.mAdditionalInformation.mTemperatureSensors.mLabel',
Value   FLOAT       '$.mAdditionalInformation.mTemperatureSensors.mValue'
)

它返回的两行都为空，为什么要这样做？我想让它提取mTemperatureSensors数据中的每个元素。

Unit    Label   Value
NULL    NULL    NULL
NULL    NULL    NULL

Answer 1

select s.*
from openjson(@json)
with
(
mTemperatureSensors nvarchar(max) '$.mAdditionalInformation.mTemperatureSensors' as json
) as t
cross/*outer*/ apply openjson(t.mTemperatureSensors)
with
(
mLabel nvarchar(20)
) as s

Answer 2

一个选项是在将@json的内容插入表(tab(后，通过CROSS APPLY逐步应用OPENJSON：

SELECT  Unit, Label, Value
FROM tab
CROSS APPLY OPENJSON(JsonData)
WITH (
TempSens NVARCHAR(MAX) '$.mAdditionalInformation.mTemperatureSensors' AS JSON ) Q1 
CROSS APPLY OPENJSON (Q1.TempSens) 
WITH (
Unit   NVARCHAR(MAX) '$.mUnit',
Label  NVARCHAR(MAX) '$.mLabel',
Value  FLOAT         '$.mValue'
) Q2    

演示

或声明标量变量@json，如您的情况：

SELECT Unit, Label, Value
FROM OPENJSON(@json)
WITH (
TempSens NVARCHAR(MAX) '$.mAdditionalInformation.mTemperatureSensors' AS JSON ) Q1
CROSS APPLY OPENJSON (Q1.TempSens) 
WITH (
Unit   NVARCHAR(MAX) '$.mUnit',
Label  NVARCHAR(MAX) '$.mLabel',
Value  FLOAT         '$.mValue'
) Q2 

演示