json - 当路径正确时,OPENJSON() SQL SERVER 返回 null
问题描述
这是我声明的 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 -> mAdditionalInformation -> mTemperatureSensors。
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'
)
它返回 2 行都为空,为什么要这样做?我希望它提取 mTemperatureSensors 数据中的每个元素。
Unit Label Value
NULL NULL NULL
NULL NULL NULL
解决方案
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
推荐阅读
- c++ - 有没有办法在不创建变量的情况下调用期望指针的函数?
- authentication - pam 从哪里获得凭证信息?
- opencl - opencl 中 atomic_add 的行为
- html - 以角度 5 在 HTML 上显示来自 json 对象的数据
- sql-server - 我想选择包含值且不包含值的 UserId
- jslider - 如何自定义 JSlider 以获得更大的“拇指”?
- ruby-on-rails - 如何在远程服务器rails上连接postgresql数据库
- redux - replaceReducer 导致reducer在redux中被多次调用
- node.js - 节点http服务器没有监听
- python - PyCharm import tensorflow 有错误但 python 控制台工作