json - TSQL——提取子对象(不是数组)中未知/动态键的 JSON 值

Question

我正在使用 DataTables、DataTables 编辑器、JavaScript 和 MSSQL 2016。

我想在 SQL Server 中解析此字符串:

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

我不知道如何访问 key “2019-08-03”。这代表主键，或数据表编辑器中的DT_RowId。它是动态的......它可能会改变。

过去，我只是将 JavaScript 中的数据操作为 FLAT 对象，这在 SQL Server 中更容易解析:

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

但是，我想知道如何使用 json_query、json_value 和 openjson() 深入了解“动态”上面提到的 key，然后访问它的值。

以下是我所有失败的尝试:

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

最明确且类型安全的方法可能是这样的:

我用两个动态键定义你的JSON

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

--查询

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;

结果

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

简单的想法:

• 第一个 OPENJSON() 将返回别名 A 下的两个第一级 key 。 data 元素返回 AS JSON，以便稍后继续处理。
• 第二个 OPENJSON() 获取 A.[data] 作为输入，并需要获取 key，这是您的日期。
• 第三个 OPENJSON() 现在获取 B.[value] 作为输入。

WITH 子句允许读取隐式旋转和键入的内部元素。

一般情况:在通用数据容器中，使用描述性部分作为内容并不是一个好主意。这是可能的，而且可能看起来很聪明，但最好将日期作为内容放在 date-key 中。