SQL Server 2017 Selecting JSON embedded within a JSON field

Question

In SQL Server 2017, I'd like to "SELECT" a JSON object embedded within another as a string so we can store/process them later.
eg JSON:

[
{"key1":"value1",
"level2_Obj":{"key2":"value12"}
},
{"key1":"value2",
"level2_Obj":{"key22":"value22"}
},
]

From above JSON, I'd like to SELECT whole of the level2Obj JSON object, see below for what I'd like to see the "selection" result.

value1  |{"key2" :"value12"}
value2  |{"key22":"value22"}

I tried below with no luck:

SELECT * FROM
OPENJSON(@json,'$."data1"')
WITH( 
    [key1]      nvarchar(50),
    [embedded_json]     nvarchar(max)   '$."level2Obj"'
) AS DAP

Can some one please help how I select the contents of the 2nd level JSON object as a string?
The idea is to Write 1st level JSON properties into individual cells and rest of JSON levels into a single column of type nvarchar(max) (i.e whole of sub-level JSON object into a single column as a string for further processing in later stages).

Your text is not properly formatted as JSON. You can use select ISJSON(@json) to confirm. You have extra comma after the last object in the array — Ronen Ariely
– Ronen Ariely, Commented Aug 22, 2018 at 11:13

Ronen Ariely · Accepted Answer · 2018-08-22 11:19:26Z

1

Good day,

Firstly, Your JSON text is not properly formatted. There is extra comma after the last object in the array. I will remove this extra comma for the sake of the answer, but if this is the format you have then first step will be to clear the text and make sure that is is well formatted.

Please check if this solve your needs:

declare @json nvarchar(MAX) = '
[
{
"key1":"value1",
"level2_Obj":{"key2":"value12"}
}
,
{
"key1":"value2",
"level2_Obj":{"key22":"value22"}
}
]
'
SELECT JSON_VALUE (t1.[value], '$."key1"'), JSON_QUERY (t1.[value], '$."level2_Obj"')
FROM OPENJSON(@json,'$') t1

answered Aug 22, 2018 at 11:19

Ronen Ariely

2,45414 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

SQL Server 2017 Selecting JSON embedded within a JSON field

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