使用CROSS APPLY OPENJSON与PIVOT时排序错误问题排查

问题描述 #

现有SQL代码解析JSON并执行PIVOT操作后，出现690与1192对应值匹配错误的问题。例如原始JSON中690:1本该对应1192:4352，但结果中却对应了1192:4228，所有配对关系均混乱。

错误原因 #

1. JSON结构破坏：原代码将原始二维数组格式的JSON（每组包含690和1192两个对象）打散为一维数组，丢失了组的关联关系。
2. 排序逻辑无效：使用ROW_NUMBER() OVER (PARTITION BY P.requestId, AttsData.[Id] ORDER BY (SELECT 1))生成的row_id是无意义的随机排序，无法保证同一组的690和1192条目配对。

修复方案 #

需要先保留原始JSON的组结构，为每个组分配序号，再解析组内数据并完成配对：

WITH request AS (
    SELECT 
        requestId,
        -- 修复原始JSON格式，转换为合法的二维数组
        '[' + REPLACE(property1191, '], [', '],[') + ']' AS json
    FROM capex_management_requests
),
grouped_data AS (
    SELECT
        r.requestId,
        -- 解析外层数组，提取每个包含690和1192的组
        JSON_QUERY(r.json, '$[' + CAST([key] AS VARCHAR(10)) + ']') AS group_json,
        -- 为每个组分配顺序序号，保留原始JSON中的组顺序
        CAST([key] AS INT) AS group_seq
    FROM request r
    CROSS APPLY OPENJSON(r.json)
),
group_details AS (
    SELECT
        gd.requestId,
        gd.group_seq,
        atts.Id,
        atts.data
    FROM grouped_data gd
    CROSS APPLY OPENJSON(gd.group_json)
    WITH (
        Id VARCHAR(200) N'$.metaId',
        data VARCHAR(200) N'$.data'
    ) AS atts
)
SELECT
    requestId,
    -- 按组序号聚合，确保同一组的690和1192值正确配对
    MAX(CASE WHEN Id = '690' THEN data END) AS [690],
    MAX(CASE WHEN Id = '1192' THEN data END) AS [1192]
FROM group_details
GROUP BY requestId, group_seq
ORDER BY requestId, group_seq;

代码说明 #

1. 修复JSON格式：将原始不完整的JSON转换为合法的二维数组，确保每个组（包含690和1192的两个对象）被正确识别。
2. 提取组并分配序号：通过OPENJSON解析外层数组，为每个组分配group_seq，保留原始JSON中的组顺序。
3. 解析组内数据：对每个组单独解析，提取metaId和data。
4. 分组配对：按requestId和group_seq分组，用CASE语句将同一组的690和1192值对应起来，最终得到正确的配对结果。

正确结果示例 #

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