TSQL -- Extract JSON values for unknown/dynamic key in sub-object (not an array)

Question

I'm using DataTables, DataTables Editor, JavaScript, and MSSQL 2016.

I'd like to parse this string in SQL Server:

{
    "action":"edit",
    "data": { 
                "2019-08-03":{ 
                                "Description":"sdfsafasdfasdf",
                                "FirstFrozenStep":"333"
                             }
            }
}

I don't know how to access the key "2019-08-03". This represents the primary key, or the DT_RowId in DataTables Editor. It's dynamic... It could change.

Historically, I have just manipulated the data in JavaScript to a FLAT object, which is WAY easier to parse in SQL Server:

{
     "action":"edit",
     "DT_RowId":"2019-08-03",
     "Description":"sdfsafasdfasdf",
     "FirstFrozenStep":"333"
}

HOWEVER, I would like to know how to use json_query, json_value, and openjson() to drill down to the "dynamic" key mentioned above, and then access its values.

Here are all my FAILED attempts:

declare
    @jsonRequest nvarchar(max) = '{"action":"edit","data":{"2019-08-03":{"Description":"sdfsafasdfasdf","FirstFrozenStep":"333"}}}'
    ,@json2 nvarchar(max) = '{"2019-08-03":{"Description":"sdfsafasdfasdf","FirstFrozenStep":"333"}}'
    ,@jsonEASY nvarchar(max) = '{"action":"edit","DT_RowId":"2019-08-03","Description":"sdfsafasdfasdf","FirstFrozenStep":"333"}'


    select
        json_value(@jsonRequest, '$.action') as [action]
        --,json_value(@jsonRequest, '$.data.[0]') as [action]
        --,json_query(@jsonRequest, '$.data[0]')
        --,json_query(@jsonRequest, '$.data.[0]')
        --,json_query(@jsonRequest, '$.data[0].Description')
        --,json_query(@jsonRequest, '$.data.Description')
        --,json_query(@jsonRequest, '$.data.[0].Description')


select
    [Key]
    ,Value
    ,Type
    --,json_query(value, '$')
from
    openjson(@jsonRequest)



SELECT x.[Key], x.[Value]
FROM OPENJSON(@jsonRequest, '$') AS x;

select
    x.[Key]
    ,x.[Value]
    --,json_query(x.value, '$')
    --,(select * from openjson(x.value))
FROM OPENJSON(@jsonRequest, '$') AS x;

SELECT x.[Key], x.[Value]
FROM OPENJSON(@json2, '$') AS x;


    select
        json_value(@jsonEASY, '$.action') as [action]
        ,json_value(@jsonEASY, '$.DT_RowId') as [DT_RowId]
        ,json_value(@jsonEASY, '$.Description') as [Description]

Answer 1

The most explicit and type-safe approach might be this:

I define your JSON with two dynamic keys

DECLARE @json NVARCHAR(MAX)=
N'{
    "action":"edit",
    "data": { 
                "2019-08-03":{ 
                                "Description":"sdfsafasdfasdf",
                                "FirstFrozenStep":"333"
                             },
                "2019-08-04":{ 
                                "Description":"blah4",
                                "FirstFrozenStep":"444"
                             }
            }
}';

--the query

SELECT A.[action]
      ,B.[key]
      ,C.*
FROM OPENJSON(@json) WITH([action]  NVARCHAR(100)
                         ,[data]    NVARCHAR(MAX) AS JSON) A
OUTER APPLY OPENJSON(A.[data]) B 
OUTER APPLY OPENJSON(B.[value]) WITH([Description] NVARCHAR(100)
                                     ,FirstFrozenStep INT) C;

The result

action  key         Description     FirstFrozenStep
edit    2019-08-03  sdfsafasdfasdf  333
edit    2019-08-04  blah4           444

The idea in short:

• The first OPENJSON() will return the two first-level-keys under the alias A. The data element is returned AS JSON, which allows to proceed with this later.
• The second OPENJSON() gets A.[data] as input and is needed to get hands on the key, which is your date.
• The third OPENJSON() now gets B.[value] as input.

The WITH-clause allows to read the inner elements implicitly pivoted and typed.

In general: In generic data containers it is no good idea to use descriptive parts as content. This is possible and might look clever, but it was much better to place your date as content within a date-key.

Answer 2

You can use OUTER APPLY to get to the next level in JSON:

SELECT L1.[key], L2.[key], L2.[value]
FROM openjson(@json,'$.data') AS L1
OUTER APPLY openjson(L1.[value]) AS L2

It will return:

key         key             value  
2019-08-03  Description     sdfsafasdfasdf  
2019-08-03  FirstFrozenStep 333