JSON to SQL - Extracting from JSON Array?

Question

I have an exercise to extract some data from a larger JSON object however the data is added as multiple objects or perhaps an array of sorts.

An example below;

DECLARE @json NVARCHAR(MAX) = '{
"N.data.-ce731645-e4ef-4784-bc02-bb90b4c9e9e6": "Some Data",
"N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f": [
    {
        "date_1": "2018-10-20T23:00:00.000Z"
    },
    {
        "date_1": "2018-10-21T23:00:00.000Z"
    }
]
}'

I need to extract these datetime entries from the "date_1" identifier into ideally a CSV list. From that I can do my own manipulations.

2018-10-20T23:00:00.000Z, 2018-10-21T23:00:00.000Z

I am familiar with JSON_VALUE() however not with its use outside of a simple piece of one dimensional data.

What I have so far;

DECLARE @json NVARCHAR(MAX) = '{
"N.data.-ce731645-e4ef-4784-bc02-bb90b4c9e9e6": "Some Data",
"N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f": [
    {
        "date_1": "2018-10-20T23:00:00.000Z"
    },
    {
        "date_1": "2018-10-21T23:00:00.000Z"
    }
]
}'

SELECT value FROM OPENJSON(@json)

Is there a way to achive the expected output outside of complex substring() and replace() uses?

Using SQL Server 2017

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 <X64> (Build 9600: ) (Hypervisor) 

Thanks

Answer 1

Since SQL Server 2017, the extraction can be done via native OPENJSON:

DECLARE @json NVARCHAR(MAX) = '{
"N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f": [
    {
        "date_1": "2018-10-20T23:00:00.000Z"
    },
    {
        "date_1": "2018-10-21T23:00:00.000Z"
    }
]
}'


SELECT  
    JSON_VALUE(child_value.value, '$.date_1') AS [key]    
FROM OPENJSON(@json, '$') AS nda
cross apply openjson(nda.value, '$') as child_value

Results to:

key
2018-10-20T23:00:00.000Z
2018-10-21T23:00:00.000Z

Is there a way to adjust this to extract the values for a specific key, "N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f" in the example

In this case, that query can be slightly simplified to:

DECLARE @id nvarchar(200) = 'N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f'

SELECT  
    JSON_VALUE(nda.value, '$.date_1') AS [key]    
FROM OPENJSON(@json, concat('$."',@id,'"')) AS nda

or without parametrization:

SELECT  
    JSON_VALUE(nda.value, '$.date_1') AS [key]    
FROM OPENJSON(@json, '$."N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f"') AS nda

Answer 2

Use a cross apply with OPENJSON() using a with_clause:

DECLARE @json NVARCHAR(MAX) = '{
"N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f": [
    {
        "date_1": "2018-10-20T23:00:00.000Z"
    },
    {
        "date_1": "2018-10-21T23:00:00.000Z"
    }
]
}';

SELECT [b].*
FROM   OPENJSON(@json) [a]
CROSS APPLY
       OPENJSON([a].[Value])
           WITH (
                    [date_1] DATETIME '$.date_1'
                ) [b];

Answer 3

Another possible approach, using OPENJSON(). With this approach you can get key/value pairs from your nested JSON array, even if this array has different key names.

DECLARE @json nvarchar(max)
SET @json = 
N'{"N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f": [
    {
        "date_1": "2018-10-20T23:00:00.000Z"
    },
    {
        "date_1": "2018-10-21T23:00:00.000Z"
    },
    {
        "date_2": "2019-10-21T23:00:00.000Z"
    }
]
}'

SELECT 
   x.[key] AS SessionData, 
   z.[key],
   z.[value]
FROM OPENJSON(@json) x
CROSS APPLY (SELECT * FROM OPENJSON(x.[value])) y
CROSS APPLY (SELECT * FROM OPENJSON(y.[value])) z
--WHERE z.[key] = 'date_1'

Output:

SessionData                                                 key     value
N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f    date_1  2018-10-20T23:00:00.000Z
N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f    date_1  2018-10-21T23:00:00.000Z
N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f    date_2  2019-10-21T23:00:00.000Z

Update: If you want to filter by key name, next may help:

DECLARE @json NVARCHAR(MAX) = '{
"N.data.-ce731645-e4ef-4784-bc02-bb90b4c9e9e6": "Some Data",
"N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f": [
    {
        "date_1": "2018-10-20T23:00:00.000Z"
    },
    {
        "date_1": "2018-10-21T23:00:00.000Z"
    }
]
}'

SELECT z.[value]
--SELECT STRING_AGG(z.[value], ', ') [Data] -- with string aggregation
FROM OPENJSON(@json) x
CROSS APPLY (SELECT * FROM OPENJSON(x.[value])) y
CROSS APPLY (SELECT * FROM OPENJSON(y.[value])) z
WHERE x.[key] = 'N.data.sessionDates-7f1790d3-9175-43aa-962b-161ee3b8615f'

Output:

value
2018-10-20T23:00:00.000Z
2018-10-21T23:00:00.000Z
-- With string aggregation
--Data
--2018-10-20T23:00:00.000Z, 2018-10-21T23:00:00.000Z