在Microsoft SQL Server中提取JSON字符串列至多列的方法

你的JSON列是JSON数组，直接用JSON_VALUE会返回NULL，因为它只能定位单个JSON对象的属性，无法直接遍历数组。需要先解析数组为行数据，再转成目标列结构。

步骤1：用OPENJSON解析数组 #

OPENJSON可以把JSON数组拆分成包含Name和Value的行数据：

SELECT 
    t.Id, -- 替换为你的表主键/唯一标识列
    j.Name,
    j.Value
FROM YourTable t
CROSS APPLY OPENJSON(t.JsonColumn)
WITH (
    Name NVARCHAR(100) '$.Name',
    Value NVARCHAR(MAX) '$.Value'
) j

步骤2：用PIVOT转成列 #

将行数据转置为你需要的多列结构：

SELECT 
    Id,
    ISNULL(Id1, '') AS Id1,
    ISNULL(Id2, '') AS Id2,
    ISNULL(Id3, '') AS Id3,
    ISNULL(Id4, '') AS Id4,
    ISNULL(Quantity, '') AS Quantity,
    ISNULL(Id5, '') AS Id5
FROM (
    SELECT 
        t.Id,
        j.Name,
        j.Value
    FROM YourTable t
    CROSS APPLY OPENJSON(t.JsonColumn)
    WITH (
        Name NVARCHAR(100) '$.Name',
        Value NVARCHAR(MAX) '$.Value'
    ) j
) src
PIVOT (
    MAX(Value)
    FOR Name IN (Id1, Id2, Id3, Id4, Quantity, Id5)
) pvt

关键说明 #

• 替换YourTable为实际表名，JsonColumn为JSON列名，Id为表的唯一标识列
• ISNULL用于处理某些Name不存在时的空值，避免结果列出现NULL
• 如果JSON数组中存在重复Name，MAX(Value)会取最后一个匹配值，若需去重可先对源数据做处理

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