json - 如何使用 SQL 解析 JSON 列

Question

我正在尝试使用 T-SQL 将一个特定的、有效的 JSON 字符串从列解析为它的各个值。

我看过很多样本，尤其是这个 Parse JSON in TSQL仍然不在那里。任何人都可以建议一个有效的 T-SQL 语句来完成这项工作吗？

在 MessageDetail 列中使用 json 示例 cte:

select Id, MessageDetail from cte_example

Id  MessageDetail
1   {"@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance", "sysadmins": {"sysadmin": {"Member": "DummyAdmin", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-22T18:10:55.023", "Removed": "2019-07-22T19:21:15.867"}}}

我正在尝试使用的 T-SQL:

select
    json_value(b.value, '$.Member') as Member
    ,json_value(b.value, '$.IsDisabled') as IsDisabled
    ,json_value(b.value, '$.IsNTGroup') as IsNTGroup
    ,json_value(b.value, '$.Added') as Added
    ,json_value(b.value, '$.Removed') as Removed
from
    cte_example a
    outer apply openjson(json_query(a.MessageDetail, '$.sysadmins.sysadmin')) b

导致以下错误:

Msg 13609, Level 16, State 2, Line 17 JSON text is not properly formatted. Unexpected character 'D' is found at position 0.

我发现 JSON 查询 $.sysadmins.sysadmin 是有效的，这让我感到困惑。我做错了什么？

注意:当它尝试解析以下内容时，我的查询工作正常

{"@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance", "sysadmins": {"sysadmin": [{"Member": "sa", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T03:01:49.677"}, {"Member": "testuser", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T03:01:49.677"}, {"Member": "abc\\User1", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T03:01:49.677"}, {"Member": "NT SERVICE\\SQLWriter", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T03:01:49.677"}, {"Member": "NT SERVICE\\Winmgmt", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T03:01:49.677"}, {"Member": "NT Service\\MSSQLSERVER", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T03:01:49.677"}, {"Member": "NT SERVICE\\SQLSERVERAGENT", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T03:01:49.677"}, {"Member": "DummyAdmin", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-22T02:10:07.833", "Removed": "2019-07-22T03:00:02.177"}, {"Member": "domain1\\testservice", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-19T04:18:51.900"}, {"Member": "##MS_PolicyEventProcessingLogin##", "IsDisabled": "1", "IsNTGroup": "0", "Added": "2019-07-22T04:07:48.497"}]}}

Answer 1

解决方案一:

如果你想使用 JSON_VALUE，在这种情况下你需要的是使用带有显式模式的 OPENJSON 和 AS JSON 选项列定义。这里的path参数是$.sysadmins:

表格:

CREATE TABLE cte_example (
   Id int,
   MessageDetail nvarchar(max)
)
INSERT INTO cte_example
   (Id, MessageDetail)
VALUES   
   (1, N'{"@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance", "sysadmins": {"sysadmin": {"Member": "DummyAdmin", "IsDisabled": "0", "IsNTGroup": "0", "Added": "2019-07-22T18:10:55.023", "Removed": "2019-07-22T19:21:15.867"}}}')

声明:

SELECT 
   JSON_VALUE(b.sysadmin, '$.Member') AS Member,
   JSON_VALUE(b.sysadmin, '$.IsDisabled') AS IsDisabled,
   JSON_VALUE(b.sysadmin, '$.IsNTGroup') AS IsNTGroup,
   JSON_VALUE(b.sysadmin, '$.Added') AS Added,
   JSON_VALUE(b.sysadmin, '$.Removed') AS Removed
FROM cte_example a
OUTER APPLY OPENJSON(a.MessageDetail, '$.sysadmins') WITH (
   sysadmin nvarchar(max) '$.sysadmin' AS JSON
) b

输出:

-------------------------------------------------------------------
Member      IsDisabled  IsNTGroup   Added                   Removed
-------------------------------------------------------------------
DummyAdmin  0           0           2019-07-22T18:10:55.023 2019-07-22T19:21:15.867

解决方案 2:

您可以尝试另一种方法，不使用 JSON_VALUE，再次使用具有显式模式定义的 OPENJSON。这里的path参数是$.sysadmins.sysadmin:

SELECT b.*
FROM cte_example a
OUTER APPLY OPENJSON(a.MessageDetail, '$.sysadmins.sysadmin') WITH (
   Member nvarchar(10) '$.Member', 
   IsDisabled nvarchar(1) '$.IsDisabled', 
   IsNTGroup nvarchar(1) '$.IsNTGroup', 
   Added nvarchar(23) '$.Added', 
   Removed nvarchar(23) '$.Removed'
) b

对错误的解释:

With语句:

select
    b.*
from
    cte_example a
    outer apply openjson(json_query(a.MessageDetail, '$.sysadmins.sysadmin')) b

你的结果是:

---------------------
key value   type 
---------------------
Member      DummyAdmin              1
IsDisabled  0                       1
IsNTGroup   0                       1
Added       2019-07-22T18:10:55.023 1
Removed     2019-07-22T19:21:15.867 1

value 列中的值不是 JSON 格式，json_value(b.value, '$.Member') 返回错误。