如何在SQL Server列的JSON集合中提取指定属性？

核心方案：用OPENJSON()拆解数组 #

JSON_VALUE仅适用于单个JSON对象，处理数组时需要借助OPENJSON()将数组拆分为行集，再逐个提取目标字段。

示例查询代码 #

假设你的表名为YourTable，执行以下SQL即可得到Id与对应name的一一对应结果：

SELECT 
    t.Id,
    JSON_VALUE(j.value, '$.name') AS Name
FROM 
    YourTable t
CROSS APPLY 
    OPENJSON(t.ItemDetails) j

代码说明 #

• CROSS APPLY：将原表每行数据与OPENJSON()生成的行集关联，把每个JSON数组中的对象拆分为独立行。
• OPENJSON(t.ItemDetails)：把ItemDetails列的JSON数组转换为包含value列的行集，每个value对应数组中的一个JSON对象。
• JSON_VALUE(j.value, '$.name')：从拆分后的单个JSON对象中提取name字段的值。

测试验证 #

若表中存在如下数据：

执行查询后会得到结果：

额外处理：过滤非法JSON #

如果存在格式不合法的JSON数据，可添加ISJSON()过滤：

SELECT 
    t.Id,
    JSON_VALUE(j.value, '$.name') AS Name
FROM 
    YourTable t
CROSS APPLY 
    OPENJSON(t.ItemDetails) j
WHERE 
    ISJSON(t.ItemDetails) = 1

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