SQL Server解析多JSON数据后排列异常的问题排查与解决

示例场景还原 #

原始数据（表与JSON字段） #

假设存在表JsonDataTable，其中JsonContent字段存储如下JSON数组：

[
  {"Id": 1, "FieldName": "Name", "FieldValue": "Alice"},
  {"Id": 1, "FieldName": "Age", "FieldValue": "30"},
  {"Id": 2, "FieldName": "Name", "FieldValue": "Bob"},
  {"Id": 2, "FieldName": "Age", "FieldValue": "25"}
]

错误SQL语句 #

用户尝试使用以下SQL解析：

WITH CTE AS (
    SELECT 
        JSON_VALUE(value, '$.FieldName') AS FieldName,
        JSON_VALUE(value, '$.FieldValue') AS FieldValue
    FROM JsonDataTable
    CROSS APPLY OPENJSON(JsonContent)
)
SELECT *
FROM CTE
PIVOT (
    MAX(FieldValue)
    FOR FieldName IN ([Name], [Age])
) AS PivotTable;

当前错误结果 #

期望正确结果 #

错误逻辑排查 #

核心问题是CTE中未保留用于分组的唯一标识字段：

• 原SQL仅提取了FieldName和FieldValue，丢失了区分不同数据行的关键关联键（示例中的Id）。
• PIVOT操作在没有明确分组键的情况下，会对所有数据进行无差别聚合，导致不同行的字段值被错误交叉匹配。

修正后的SQL语句 #

场景1：JSON数组包含行标识字段 #

WITH CTE AS (
    SELECT 
        JSON_VALUE(value, '$.Id') AS Id, -- 保留行唯一标识
        JSON_VALUE(value, '$.FieldName') AS FieldName,
        JSON_VALUE(value, '$.FieldValue') AS FieldValue
    FROM JsonDataTable
    CROSS APPLY OPENJSON(JsonContent)
)
SELECT Id, [Name], [Age]
FROM CTE
PIVOT (
    MAX(FieldValue)
    FOR FieldName IN ([Name], [Age])
) AS PivotTable;

场景2：JSON数组嵌套在表的每行记录中（表自身有主键） #

如果表结构为每行存储一个独立的JSON数组（而非单字段存大数组），需保留表的主键作为分组键：

-- 示例表结构
CREATE TABLE JsonDataTable (
    RowId INT PRIMARY KEY,
    JsonContent NVARCHAR(MAX)
);

-- 示例数据
INSERT INTO JsonDataTable VALUES
(1, '[
  {"FieldName": "Name", "FieldValue": "Alice"},
  {"FieldName": "Age", "FieldValue": "30"}
]'),
(2, '[
  {"FieldName": "Name", "FieldValue": "Bob"},
  {"FieldName": "Age", "FieldValue": "25"}
]');

-- 修正SQL
WITH CTE AS (
    SELECT 
        j.RowId, -- 用表主键作为行标识
        JSON_VALUE(value, '$.FieldName') AS FieldName,
        JSON_VALUE(value, '$.FieldValue') AS FieldValue
    FROM JsonDataTable j
    CROSS APPLY OPENJSON(j.JsonContent)
)
SELECT RowId, [Name], [Age]
FROM CTE
PIVOT (
    MAX(FieldValue)
    FOR FieldName IN ([Name], [Age])
) AS PivotTable;

关键修正点说明 #

1. 必须保留行唯一标识：无论是JSON内部的关联键（如Id）还是表自身的主键，这个字段是PIVOT时正确分组的核心，确保同一行的字段值被聚合到同一结果行。
2. 明确PIVOT的聚合逻辑：使用MAX()或MIN()聚合函数时，因为同一分组内每个FieldName只会对应一个FieldValue，所以聚合结果不会失真。
3. 扩展适配：如果JSON中有更多字段，只需在CTE中提取对应字段名，并在PIVOT的IN子句中添加对应的列名即可。

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