从InvoiceDisputesApprovalResult表获取指定disputeId审批人JSON失败

我有一张用于工作流的InvoiceDisputesApprovalResult表，字段包括disputeId、approverEmail、status、approvalLevel和claimId。需求是根据指定disputeId生成特定格式的JSON，其中disputeType固定为"CREAR_NC"、status固定为"OK"等部分字段为固定值，approvers数组对应该disputeId下的所有审批人信息。

示例数据与期望输出 #

示例1 #

当claimId=3423、disputeId=3123123时，表中数据：

disputeId  |approverEmail           |status  |approvalLevel|claimId
3123123    |approver1@mailinator.com|waiting |1            |3423 
3123123    |approver2@mailinator.com|waiting |2            |3423 
3123123    |approver3@mailinator.com|waiting |3            |3423 

期望生成的JSON：

{   
    "claimId": 3423,  
    "disputeId": 3123123,   
    "disputeType": "CREAR_NC",  
    "status": "OK",  
    "description": "Aprobador",   
    "approvers": [  
        {"mail": "approver1@mailinator.com", "approvalLevel": 1},
        {"mail": "approver2@mailinator.com", "approvalLevel": 2},
        {"mail": "approver3@mailinator.com", "approvalLevel": 3}
    ], 
    "pvp": null,
    "salePrice": null,
    "saleMKUP": null,
    "claimMKUP": null,
    "mktope": null,
    "mkcliete": null    
}

示例2 #

当disputeId=221322时，表中数据：

disputeId |approverEmail           |status  |approvalLevel|claimId
221322    |approver1@mailinator.com|waiting |1            |2323
221322    |approver2@mailinator.com|waiting |2            |2323

期望生成对应格式的JSON，固定字段值同上。

当前问题 #

我编写的SQL语句返回了大量不属于指定disputeId的审批人，请求修正SQL以获取正确结果。

原SQL：

SELECT
    main.claimId,
    main.disputeId,
    'CREAR_NC' AS disputeType,
    'OK' AS status,
    'Aprobador' AS description,
    (
        SELECT
            '[' + STUFF(
                (
                    SELECT
                        ', ' + (
                            SELECT '{"mail": "' + sub.approverEmail + '", "approvalLevel": ' + CAST(sub.approvalLevel AS VARCHAR) + '}'
                            FOR XML PATH(''), TYPE
                        ).value('.', 'VARCHAR(MAX)')
                    FROM InvoiceDisputesApprovalResult AS sub
                    WHERE sub.claimId = main.claimId
                    and main.disputeId = 28258
                    FOR XML PATH('')
                ), 1, 2, ''
            ) + ']'
    ) AS approvers,
    NULL AS pvp,
    NULL AS salePrice,
    NULL AS saleMKUP,
    NULL AS claimMKUP,
    NULL AS mktope,
    NULL AS mkcliete
FROM InvoiceDisputesApprovalResult AS main
WHERE main.status = 'WAITING'
  AND main.disputeId = 28258 
GROUP BY main.claimId, main.disputeId;

1. 子查询中错误地硬编码了main.disputeId = 28258，应该关联sub.disputeId = main.disputeId，否则会固定拉取该disputeId的数据，无法和外层主查询的目标disputeId匹配。
2. 子查询仅通过sub.claimId = main.claimId关联，未限制sub.disputeId，导致拉取同一claimId下所有dispute的审批人，而非当前指定disputeId的审批人。

方案1：兼容低版本SQL Server（使用XML方法） #

SELECT
    main.claimId,
    main.disputeId,
    'CREAR_NC' AS disputeType,
    'OK' AS status,
    'Aprobador' AS description,
    (
        SELECT
            '[' + STUFF(
                (
                    SELECT
                        ', ' + JSON_QUERY('{"mail": "' + sub.approverEmail + '", "approvalLevel": ' + CAST(sub.approvalLevel AS VARCHAR) + '}')
                    FROM InvoiceDisputesApprovalResult AS sub
                    WHERE sub.disputeId = main.disputeId  -- 关键：关联目标disputeId
                      AND sub.status = 'WAITING'  -- 可选：过滤等待状态的审批人
                    ORDER BY sub.approvalLevel  -- 按审批级别排序，保证顺序正确
                    FOR XML PATH(''), TYPE
                ).value('.', 'VARCHAR(MAX)'), 1, 2, ''
            ) + ']'
    ) AS approvers,
    NULL AS pvp,
    NULL AS salePrice,
    NULL AS saleMKUP,
    NULL AS claimMKUP,
    NULL AS mktope,
    NULL AS mkcliete
FROM InvoiceDisputesApprovalResult AS main
WHERE main.status = 'WAITING'
  AND main.disputeId = 28258  -- 指定目标disputeId
GROUP BY main.claimId, main.disputeId;

方案2：SQL Server 2017+ 版本（使用STRING_AGG简化） #

如果你的SQL Server版本支持STRING_AGG，可以用更简洁的写法：

SELECT
    DISTINCT main.claimId,
    main.disputeId,
    'CREAR_NC' AS disputeType,
    'OK' AS status,
    'Aprobador' AS description,
    '[' + STRING_AGG(
        JSON_QUERY('{"mail": "' + sub.approverEmail + '", "approvalLevel": ' + CAST(sub.approvalLevel AS VARCHAR) + '}'),
        ','
    ) + ']' AS approvers,
    NULL AS pvp,
    NULL AS salePrice,
    NULL AS saleMKUP,
    NULL AS claimMKUP,
    NULL AS mktope,
    NULL AS mkcliete
FROM InvoiceDisputesApprovalResult AS main
JOIN InvoiceDisputesApprovalResult AS sub 
    ON main.disputeId = sub.disputeId
WHERE main.status = 'WAITING'
  AND main.disputeId = 28258
GROUP BY main.claimId, main.disputeId;

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