如何在SQL Server中将JSON数组字段解析为多列结构化数据

针对你需要把存储在SQL Server表中的JSON数组拆分成最多4列（不足补Null）的需求，我整理了几种实用的SQL方案，直接就能用：

首先先创建示例测试表（方便你验证效果）：

CREATE TABLE #TempData (
    ID INT,
    Json_Data NVARCHAR(MAX)
);

INSERT INTO #TempData (ID, Json_Data)
VALUES
(1, '[{"Book_id":"6842","index":1,"type":"BOOK"},{"Book_id":"103735","index":2,"type":"BOOK"}, {"Book_id":"104253","index":3,"type":"BOOK_GIFT"}, {"Book_id":"83886","index":4,"type":"BOOK"}]'),
(2, '[{"Book_id":"688","index":1,"type":"BOOK"},{"Book_id":"548","index":2,"type":"BOOK"}]');

方案1：条件聚合（最直观易读） #

这种方法通过OPENJSON解析数组，再用CASE语句按序号匹配对应列，新手也能快速理解：

SELECT
    ID,
    MAX(CASE WHEN [index] = 1 THEN JSON_QUERY(value) END) AS Value1,
    MAX(CASE WHEN [index] = 2 THEN JSON_QUERY(value) END) AS Value2,
    MAX(CASE WHEN [index] = 3 THEN JSON_QUERY(value) END) AS Value3,
    MAX(CASE WHEN [index] = 4 THEN JSON_QUERY(value) END) AS Value4
FROM #TempData
CROSS APPLY OPENJSON(Json_Data)
WITH (
    [index] INT '$.index', -- 提取数组元素里的index字段
    value NVARCHAR(MAX) '$' AS JSON -- 保留整个元素的原始JSON字符串
)
GROUP BY ID;

关键说明： #

• JSON_QUERY用来返回原始JSON对象，避免SQL自动转义双引号导致格式混乱
• MAX聚合函数用来把同一ID的多行解析结果合并成一行，没有对应序号的列会自动显示NULL

方案2：PIVOT转置（更简洁的写法） #

如果习惯用PIVOT语法处理行转列需求，这种写法会更紧凑：

SELECT
    ID,
    [1] AS Value1,
    [2] AS Value2,
    [3] AS Value3,
    [4] AS Value4
FROM (
    SELECT
        ID,
        [index],
        JSON_QUERY(value) AS Json_Object
    FROM #TempData
    CROSS APPLY OPENJSON(Json_Data)
    WITH (
        [index] INT '$.index',
        value NVARCHAR(MAX) '$' AS JSON
    )
) AS SourceData
PIVOT (
    MAX(Json_Object)
    FOR [index] IN ([1], [2], [3], [4]) -- 指定要转置的序号值
) AS PivotTable;

特殊场景处理：数组元素无连续index #

如果你的JSON数组里的index字段不连续，或者根本没有index字段，可以用ROW_NUMBER()生成顺序序号来替代：

SELECT
    ID,
    MAX(CASE WHEN RowNum = 1 THEN JSON_QUERY(value) END) AS Value1,
    MAX(CASE WHEN RowNum = 2 THEN JSON_QUERY(value) END) AS Value2,
    MAX(CASE WHEN RowNum = 3 THEN JSON_QUERY(value) END) AS Value3,
    MAX(CASE WHEN RowNum = 4 THEN JSON_QUERY(value) END) AS Value4
FROM #TempData
CROSS APPLY (
    SELECT
        JSON_QUERY(value) AS value,
        -- 按数组原始顺序生成1、2、3...的序号
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
    FROM OPENJSON(Json_Data)
) AS ParsedJson
GROUP BY ID;

这几种方案都能输出你想要的结果，测试通过后直接替换成你的真实表名即可。

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