如何在SQL Server中将表内嵌套JSON数组解析展开为多行数据

你之前的写法JSON_VALUE(JsonObject, '$.Messages.Sender')无法返回结果，是因为Messages是数组类型，不是对象类型，不能直接访问子属性，必须先将数组拆分为单个元素的行才能提取对应属性。

实现数组拆分为多行的方案如下：
使用SQL Server内置的OPENJSON函数配合CROSS APPLY关联即可实现需求，查询语句如下：

SELECT 
    JSON_VALUE(t.JsonObject, '$.Id') AS Id,
    JSON_VALUE(t.JsonObject, '$.Provider') AS Provider,
    JSON_VALUE(msg.value, '$.Sender') AS Sender,
    JSON_VALUE(msg.value, '$.Text') AS Text
FROM
    dbo.t
CROSS APPLY OPENJSON(t.JsonObject, '$.Messages') AS msg

参数说明：

• OPENJSON(t.JsonObject, '$.Messages') 会定位到JSON中的Messages数组，将数组内的每一个元素转为单独的行，返回结果的value字段存储单个Message对象的JSON内容
• CROSS APPLY 会将原表的每一行和其对应的Messages数组拆解出的所有行做关联，最终实现每个Message单独展示为一行的效果
• 如果你需要保留Messages数组为空或者不存在的原表记录，可以将CROSS APPLY替换为OUTER APPLY，此时对应的Sender和Text字段会返回NULL，不会过滤这部分行

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