By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tab( JsonData VARCHAR(8000) );
-- DECLARE @json VARCHAR(MAX) = N'
INSERT INTO tab VALUES('[
{
"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,
1 rows affected
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
| Unit | Label | Value |
|---|---|---|
| C | aute in | -74579140.6411169 |
| C | ullamco labore dolore | -91870052.84894 |
| F | reprehenderit consectetur id ipsum | 22634605.5384114 |
| C | magna consectetur esse | 72633803.4426956 |