CODEWITHSUNDEEP
sqljsonsql-server
user9891471
user9891471

Reputation: 65

Extracting values from JSON text in SQL Server

{"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.

Upvotes: 3

Views: 18999

Answers (1)

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 3

Related Questions