使用T-SQL提取JSON数据并实现rows行转列的技术求助

JSON_VALUE只能提取JSON中的标量值（字符串、数字、布尔值、null），而$.tables[0].rows[0]是一个嵌套数组（复合类型），不符合它的提取规则，所以返回NULL。

1. 提取单个行内的具体字段 #

如果只需要获取某一行里的特定值，直接指定数组的具体索引即可：

-- 获取第一行的timestamp
SELECT JSON_VALUE(RawJSON, '$.tables[0].rows[0][0]') AS timestamp;

-- 获取第一行的message
SELECT JSON_VALUE(RawJSON, '$.tables[0].rows[0][1]') AS message;

2. 将rows数组转成结构化表格 #

要把所有行数据转列为匹配columns字段的表格，用OPENJSON结合WITH子句解析：

SELECT 
    [timestamp],
    message,
    severityLevel,
    FriendlySeverityLevel,
    CycleId
FROM 
    OPENJSON(RawJSON, '$.tables[0].rows')
WITH (
    [timestamp] DATETIME '$[0]',
    message NVARCHAR(MAX) '$[1]',
    severityLevel INT '$[2]',
    FriendlySeverityLevel NVARCHAR(100) '$[3]',
    CycleId UNIQUEIDENTIFIER '$[4]'
);

这个查询会把每个子数组转换成一行，元素对应columns定义的字段名。

针对Azure Data Explorer（Kusto）的处理方式 #

如果用Kusto，可通过parse_json+mv-expand实现：

datatable(RawJSON:string)
[
    '{ "tables": [ { "name": "PrimaryResult", "columns": [ { "name": "timestamp", "type": "datetime" }, { "name": "message", "type": "string" }, { "name": "severityLevel", "type": "int" }, { "name": "FriendlySeverityLevel", "type": "string" }, { "name": "CycleId", "type": "dynamic" } ], "rows": [ [ "2024-07-25T12:00:00", "Report Started - User: user1@test.com MrmClient: 400600 CcID: 000a000b-0000-111c-0a0b-11111a1111a1", 1, "Information", "000a000b-0000-111c-0a0b-11111a1111a1" ], [ "2024-07-24T12:00:00", "Report Started - User: user2@test.com MrmClient: 807231 CcID: 000c000d-0000-11c1-0c0d-11111b11111b", 1, "Information", "000c000d-0000-11c1-0c0d-11111b11111b" ] ] } ] }'
]
| extend tables = parse_json(RawJSON).tables[0]
| mv-expand rows = tables.rows
| project 
    timestamp = todatetime(rows[0]),
    message = tostring(rows[1]),
    severityLevel = toint(rows[2]),
    FriendlySeverityLevel = tostring(rows[3]),
    CycleId = toguid(rows[4])

内容的提问来源于stack exchange，提问作者James