在TSQL 2019中将JSON数组值拆分为单行的实现方法

假设你的JSON payload结构类似如下（以用户信息和兴趣数组为例）：

{"userId": 101, "userName": "Andreas", "interest": ["hiking", "reading", "coding"]}

要将顶层字段与interest数组中的每个值一一对应生成多行记录，核心是用OPENJSON解析数组，并通过CROSS APPLY关联顶层数据。

单条JSON字符串处理 #

如果是处理单个JSON变量，执行以下SQL：

DECLARE @payload NVARCHAR(MAX) = N'{"userId": 101, "userName": "Andreas", "interest": ["hiking", "reading", "coding"]}';

SELECT
    JSON_VALUE(@payload, '$.userId') AS userId,
    JSON_VALUE(@payload, '$.userName') AS userName,
    interest.value AS interest
FROM
    OPENJSON(@payload, '$.interest') AS interest;

执行后会得到3行结果，每行对应一个兴趣值，顶层字段userId和userName会重复显示。

表中JSON字段批量处理 #

如果你的JSON数据存储在表的字段中（比如表UserInfo包含JsonData列），用CROSS APPLY关联表数据和数组解析结果：

SELECT
    JSON_VALUE(JsonData, '$.userId') AS userId,
    JSON_VALUE(JsonData, '$.userName') AS userName,
    interest.value AS interest
FROM
    UserInfo
CROSS APPLY
    OPENJSON(JsonData, '$.interest') AS interest;

处理数组内的对象结构 #

如果interest数组中存储的不是字符串而是对象（比如带属性的兴趣项），可以用OPENJSON的WITH子句解析对象字段：

DECLARE @payload NVARCHAR(MAX) = N'{"userId": 101, "userName": "Andreas", "interest": [{"name":"hiking","level":"high"},{"name":"reading","level":"medium"}]}';

SELECT
    JSON_VALUE(@payload, '$.userId') AS userId,
    JSON_VALUE(@payload, '$.userName') AS userName,
    interest.name,
    interest.level
FROM
    OPENJSON(@payload, '$.interest')
    WITH (
        name NVARCHAR(50) '$.name',
        level NVARCHAR(20) '$.level'
    ) AS interest;

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