如何从无命名JSON数组中提取指定属性到SQL字段？

报错原因 #

你写的JSON_VALUE(JSON_QUERY(parts,'$[0]'),'$[0].Code')存在逻辑错误：

• JSON_QUERY(parts,'$[0]')已经提取了数组的第一个JSON对象（不是数组），后续JSON_VALUE的路径应该用'$.Code'而非'$[0].Code'，后者会把单个对象当成数组处理，导致JSON格式错误。

正确提取指定Type属性的方法 #

要根据Type匹配提取对应属性，需要用OPENJSON解析整个parts数组，再结合条件筛选或聚合来生成目标字段。

方法1：使用条件聚合（适合单条记录提取多类型属性） #

SELECT 
    carId,
    carType,
    -- 提取Type为'Brakes'的Code值
    MAX(CASE WHEN j.Type = 'Brakes' THEN j.Code END) AS BrakesCode,
    -- 提取Type为'Doors'的Value值
    MAX(CASE WHEN j.Type = 'Doors' THEN j.Value END) AS DoorsValue
FROM [Assets].[dbo].[Cars]
CROSS APPLY OPENJSON(parts)
WITH (
    Value NVARCHAR(MAX) '$.Value',
    Type NVARCHAR(50) '$.Type',
    Code NVARCHAR(50) '$.Code'
) j
GROUP BY carId, carType

方法2：使用CROSS APPLY + 筛选（适合单条记录提取单个类型属性，或需保留多条匹配记录） #

如果需要提取某一种Type的属性，或者数组中可能有多个同Type的记录，可以用以下方式：

SELECT 
    c.carId,
    c.carType,
    j.Code AS BrakesCode
FROM [Assets].[dbo].[Cars] c
CROSS APPLY OPENJSON(c.parts)
WITH (
    Type NVARCHAR(50) '$.Type',
    Code NVARCHAR(50) '$.Code'
) j
WHERE j.Type = 'Brakes'

说明 #

• OPENJSON会将JSON数组拆分成行，每行对应数组中的一个对象，通过WITH子句可以直接映射JSON属性到SQL字段。
• 方法1用MAX(CASE...)聚合，确保每个carId只返回一条记录，同时把不同Type的属性转成列；如果同个Type在数组中有多个值，MAX会取最大的，你可以根据需求换成MIN或STRING_AGG（SQL Server 2017+）来合并多个值。

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