JSON SQL Server 2016 parse

Question

I have a nested JSON as output form a form and I need to parse it in order to send it in joined tables. I cannot parse some of the data I have in the JSON: labels, rangesValues or body I get NULL anyone encountered this type?

    DECLARE @json NVARCHAR(MAX)
SET @json = '{
   "Id":"712db489",
   "label":"kjk",
   "ranges":{
      "rangeQuestion":null,
      "minRange":0,
      "maxRange":10,
      "rangeValues":[1,2],
      "hasMarks":false
   },
   "labels":[1,2],
   "options":[
      {
         "body":"Yes",
         "sequence":1
      },
      {
         "body":"No",
         "sequence":2
      }
   ]
}'

SELECT * FROM  
 OPENJSON ( @json )  
WITH (   
              label   nvarchar(250), --ok
              maxRange   nvarchar(250) '$.ranges.maxRange', --ok
              labels   nvarchar(250), -- not parsed
              rangesValues nvarchar(250) '$.ranges.rangeValues' , -- not parsed
              body   nvarchar(250) '$.options.body' -- not parsed

 )

the output should be like

minRange maxRange rangeValues
0 10 1
0 10 2

body sequence

Yes 1

No 2

And what can be done if there are 3 levels nested?

DECLARE @json NVARCHAR(MAX)

SET @json = '{
   "Id":"712db489",
   "label":"kjk",
   "ranges":{
      "rangeQuestion":null,
      "minRange":0,
      "maxRange":10,
      "rangeValues": [
                {
                    "rangeValue": 1,
                    "otherValue": 10
                },
                {
                    "rangeValue": 2,
                    "otherValue": 20
                }
            ],
      "hasMarks":false
   },
   "labels":[1,2],
   "options":[
      {
         "body":"Yes",
         "sequence":1
      },
      {
         "body":"No",
         "sequence":2
      }
   ]
}'

SELECT r.minRange,
       r.maxRange,
       rV.[value] AS rangeValue
FROM OPENJSON (@json, '$.ranges')  
     WITH (minRange int,
           maxRange int,
           rangeValues nvarchar(MAX) 
           AS JSON) r
     CROSS APPLY OPENJSON (r.rangeValues) rV;

What are you expecting back here? `$.ranges.maxRange` has an array value in it, for example `[1,2]`. Are you expecting the array, `1`, `2` or 2 rows (with the values `1` and `2`)? The others that have "failed" too are the same. Please do show us the expected results you are after. — Thom A, Dec 22 '19 at 17:55
maxRange is returning ok, but labels as I have a mix of arrays as [1,2] and also other objects like options I would need to get them in separate rows minRange maxRange rangeValues 0 10 1 0 10 2 body sequence Yes 1 No 2 — drcz, Dec 22 '19 at 19:54
So show us the results you expect. Does `1` relate to `Yes` or `No`, for example? — Thom A, Dec 22 '19 at 19:56
So you want 2 datasets? Why isn't `label` in either, when it is in your attempt? — Thom A, Dec 22 '19 at 19:58
yes, It may be this case, so to get the options in one query and ranges in another one. One example of how to do it would be helpful — drcz, Dec 22 '19 at 20:03

score 1 · Accepted Answer · answered Dec 22 '19 at 20:05

1

Seems like, from the discussion in the comments, what you are actually after is this:

SELECT r.minRange,
       r.maxRange,
       rV.[value] AS rangeValue
FROM OPENJSON (@json, '$.ranges')  
     WITH (minRange int,
           maxRange int,
           rangeValues nvarchar(MAX) AS JSON) r
     CROSS APPLY OPENJSON (r.rangeValues) rV;

SELECT *
FROM OPENJSON (@json,'$.options')
     WITH (body varchar(3),
           sequence int) o;

answered Dec 22 '19 at 20:05

Thom A

88,727
11
45
75

it works! I saw in some examples the cross apply but I did not used it. thank you – drcz Dec 22 '19 at 20:07
When I have 3 nested levels how I can extract the value to the last level? The difference is that is not an array but an object at 3rd level – drcz Jan 17 '20 at 21:47
Sounds like you should be asking another question, @drcz . – Thom A Jan 18 '20 at 11:53

score 0 · Answer 2 · answered Jan 18 '20 at 07:14

second question I solved with

DECLARE @json NVARCHAR(MAX)

SET @json = '{
   "Id":"712db489",
   "label":"kjk",
   "ranges":{
      "rangeQuestion":null,
      "minRange":0,
      "maxRange":10,
      "rangeValues": [
                {
                    "rangeValue": 1,
                    "otherValue": 10
                },
                {
                    "rangeValue": 2,
                    "otherValue": 20
                }
            ],
      "hasMarks":false
   },
   "labels":[1,2],
   "options":[
      {
         "body":"Yes",
         "sequence":1
      },
      {
         "body":"No",
         "sequence":2
      }
   ]
}'

SELECT ranges.minRange as minRange,
       ranges.maxRange as maxRange,
       rangeValues.rangeValue AS rangeValue
FROM OPENJSON (@json, '$.ranges')  
WITH (minRange int,
    maxRange int,
    rangeValues nvarchar(MAX) AS JSON
    ) as ranges
CROSS APPLY OPENJSON (ranges.rangeValues) 
with (
rangeValue int ,
otherValue int
)as rangeValues;

JSON SQL Server 2016 parse

2 Answers2