OPENJSON() SQL SERVER is returning null when the path is correct

Question

This is the json i declare:

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

This is my code to extract the temperature sensor data from the json. I thought it would've worked because the hierarchy in this json to get the temperature sensor data is 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'
)

And it returns 2 rows that are both null, why is it doing this? I want it to pull each elements in mTemperatureSensors data.

Unit    Label   Value
NULL    NULL    NULL
NULL    NULL    NULL

Hi i nteresting, not sure if this might help stackoverflow.com/questions/57321728/… — IronMan
– IronMan, Commented Sep 18, 2020 at 21:30
mTemperatureSensors is an array...you'll need an apply to access its values — lptr
– lptr, Commented Sep 18, 2020 at 21:43

lptr · Accepted Answer · 2020-09-18 22:02:41Z

0

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

answered Sep 18, 2020 at 22:02

lptr

6,8582 gold badges10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ggorlen Over a year ago

Could you add a brief explanation of how this works and why it's a good solution to the problem? Thanks.

Barbaros Özhan · Accepted Answer · 2020-09-18 22:30:12Z

One option would be applying OPENJSON step by step through CROSS APPLY after inserting the content of the @json into a table(tab) :

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

Demo

or declaring a scalar variable @json as in your case :

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

Demo

Collectives™ on Stack Overflow

OPENJSON() SQL SERVER is returning null when the path is correct

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related