SQL Server中OPENJSON路径正确却返回NULL的问题排查

你遇到的全NULL结果，核心问题出在**mTemperatureSensors是一个JSON数组**，而你当前的查询试图直接从数组对象上读取mUnit/mLabel这类属性——数组本身没有这些属性，只有数组里的每个元素对象才包含这些字段，所以SQL Server找不到对应值，自然返回NULL。

要提取数组内的每个元素数据，需要先遍历外层的卡车记录，再通过CROSS APPLY拆解内部的温度传感器数组。这里提供两种常用的可行写法：

方法一：嵌套OPENJSON（推荐） #

先解析外层的卡车数组，再通过CROSS APPLY展开每个卡车对应的温度传感器数组：

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 } ] } } ]'

SELECT 
    ts.Unit, 
    ts.Label, 
    ts.Value
FROM OPENJSON(@json) AS trucks
CROSS APPLY OPENJSON(trucks.value, '$.mAdditionalInformation.mTemperatureSensors')
WITH (
    Unit VARCHAR(15) '$.mUnit',
    Label VARCHAR(50) '$.mLabel',
    Value FLOAT '$.mValue'
) AS ts;

方法二：结合JSON_QUERY提取数组 #

先通过JSON_QUERY提取出mTemperatureSensors数组，再对数组进行解析：

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 } ] } } ]'

SELECT 
    ts.Unit, 
    ts.Label, 
    ts.Value
FROM OPENJSON(@json)
WITH (
    TemperatureSensors NVARCHAR(MAX) '$.mAdditionalInformation.mTemperatureSensors' AS JSON
) AS trucks
CROSS APPLY OPENJSON(trucks.TemperatureSensors)
WITH (
    Unit VARCHAR(15) '$.mUnit',
    Label VARCHAR(50) '$.mLabel',
    Value FLOAT '$.mValue'
) AS ts;

两种写法都会返回4行正确的温度传感器数据，而非NULL结果。

内容的提问来源于stack exchange，提问作者Kevin