Extracting values from JSON text in SQL Server

Question

{"A":100,
"B":[{"Title":"Age","Match":false},{"Title":"Height","Match":false},{"Title":"Country","Match":false},{"Title":"Gender","Match":false},{"Title":"Language","Match":True}],
"C":78}

I have a table in SQL Server database and one column (columnName) has JSON text. I would like to extract values and return result in separate columns.

I tried:

SELECT JSON_VALUE(columnName, $.A) AS Score,        -- This works --
       JSON_VALUE(columnName, $.B.Title...) AS Age, -- How can I access the Age, Match values --
       JSON_VALUE(columnName, $.C) AS FinalScore    -- This works --

I think for A and C it worked because they have one value but B has list of objects.

Zhorov · Accepted Answer · 2020-04-16 19:50:45Z

You need a statement like the following. You should parse the input JSON with OPENJSON() and explicit schema (columns definitions):

Table:

CREATE TABLE Data (JsonData varchar(1000))
INSERT INTO Data (JsonData)
VALUES ('{"A":100,
"B":[{"Title":"Age","Match":false},{"Title":"Height","Match":false},{"Title":"Country","Match":false},{"Title":"Gender","Match":false},{"Title":"Language","Match":true}],
"C":78}')

Statement:

SELECT j1.A, j1.C, j2.*
FROM Data d
CROSS APPLY OPENJSON (d.JsonData) WITH (
   A int '$.A',
   B nvarchar(max) '$.B' AS JSON,
   C int '$.C'
) j1
CROSS APPLY OPENJSON (j1.B) WITH (
   Title varchar(500) '$.Title',
   Match bit '$.Match'
) j2

Result:

A   C   Title    Match
100 78  Age      False
100 78  Height   False
100 78  Country  False
100 78  Gender   False
100 78  Language True

Note, that the reason for the NULL results is the fact, that B key in the input JSON is a JSON array, so you need to use JSON_QUERY() to get the whole JSON array and additional CROSS APPLY to parse this array. JSON_VALUE() extracts a scalar value from a JSON string and if the path points to not a scalar value, the result is NULL in lax mode and an error in strict mode.

SELECT 
   JSON_VALUE(d.JsonData, '$.A') AS A,
   JSON_QUERY(d.JsonData, '$.B') AS B,
   JSON_VALUE(d.JsonData, '$.C') AS C,
   j.*
FROM Data d
CROSS APPLY OPENJSON(d.JsonData, '$.B') WITH (
   Title varchar(500) '$.Title',
   Match bit '$.Match'
) j

Collectives™ on Stack Overflow

Extracting values from JSON text in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related