SQL Extract from JSON "object"

Question

So I have a requirement to extract some data from JSON store and for the most part its pretty simple with JSONVALUE(). However some of the stores seem to use a deeper storage method.

Some example code;

DECLARE @json NVARCHAR(MAX) = '{
"N.data.-60d8249a-ed12-4f41-98f4-01c910c6b2f4": null,
"R.title": "{\"description\":\"Mr\",\"alternates\":{},\"position\":0}",
"R.gender": "{\"description\":\"Male\",\"alternates\":{},\"position\":1}",
"R.jobTitle": "{\"description\":\"Operations\",\"alternates\":{},\"position\":2}"
}'

What I need to do is extract the "description" aspect from each key. For example, "R.title" would give me "Mr" etc.

Ideally I could extract a specific key each time using a parameter approach:

DECLARE @id nvarchar(200) = 'R.title'
SELECT  
*  
FROM OPENJSON(@json, concat('$."',@id,'"')) AS a

Is there a correct way to do this without doing nasty substring() methods?

Thanks!

SQL Server version:

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Express Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3 (Build 9600: ) (Hypervisor)

Zhorov Zhorov · Accepted Answer · 2020-03-20T08:27:16

It's probably a late answer, but I hope it helps. You need to parse the input JSON with two OPENJSON() calls and additional APPLY operator. The first OPENJSON() call uses the default schema to get the names for all keys in the input JSON.

Statement:

DECLARE @json NVARCHAR(MAX) = '{
"N.data.-60d8249a-ed12-4f41-98f4-01c910c6b2f4": null,
"R.title": "{\"description\":\"Mr\",\"alternates\":{},\"position\":0}",
"R.gender": "{\"description\":\"Male\",\"alternates\":{},\"position\":1}",
"R.jobTitle": "{\"description\":\"Operations\",\"alternates\":{},\"position\":2}"
}'


SELECT j.[key], v.description
FROM OPENJSON(@json) j
CROSS APPLY OPENJSON(j.[value]) WITH (description nvarchar(max) '$.description') v

Result:

-----------------------  
key         description
-----------------------  
R.title     Mr
R.gender    Male
R.jobTitle  Operations

SQL Extract from JSON "object"

1 Answers