时间戳

首页 > 解决方案 > 如何使用 TSQL 解析 JSON 列

json - 如何使用 TSQL 解析 JSON 列

问题描述

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

我查看了许多示例，尤其是在 TSQL 中解析 JSON的这个示例，但仍然不完全存在。任何人都可以建议一个有效的 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

这导致以下错误：

消息 13609，级别 16，状态 2，第 17 行 JSON 文本格式不正确。在位置 0 处发现了意外的字符“D”。

由于 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"}]}}

标签： jsonsql-servertsql

解决方案

解决方案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

关于您的错误的解释：

带声明：

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')返回错误。

推荐阅读