sql - 如何消除 DB2400 或任何 SQL 中 JSON_ARRAYAGG 中的重复行？

Question

SQL 示例...

SELECT
    ID AS HID,
    JSON_ARRAYAGG(    
       JSON_OBJECT('sequence': TRIM(SEQUENCE),                         
                   'payer_reference_qualifier': TRIM(PAYREFQUAL),      
                   'payer_reference_id': TRIM(PAYREFID),               
                   'transaction_processing_status': TRIM(TRNPRCST) 
                   )) AS H1
FROM XXXXX
WHERE ID = 7146 
GROUP BY MSGHSTID;

检索到的数据:我只想检索一行而不是两行。有什么想法吗？

[
    {"sequence":"1","payer_reference_qualifier":"04","payer_reference_id":"EPIDETPMT0000000001","transaction_processing_status":"E"},
    {"sequence":"1","payer_reference_qualifier":"04","payer_reference_id":"EPIDETPMT0000000001","transaction_processing_status":"E"}
]

Answer 1

一个选项在子查询中使用distinct:

SELECT 
    ID AS HID,
    JSON_ARRAYAGG(    
        JSON_OBJECT(
            'sequence': SEQUENCE,                         
            'payer_reference_qualifier': PAYREFQUAL,
            'payer_reference_id': PAYREFID,
            'transaction_processing_status': TRNPRCST
        )
    ) AS H1
FROM (
    SELECT DISINCT 
        MSGHSTID, 
        TRIM(SEQUENCE) SEQUENCE, 
        TRIM(PAYREFQUAL) PAYREFQUAL, 
        TRIM(PAYREFID) PAYREFID, 
        TRIM(TRNPRCST) TRNPRCST
    FROM XXXXX 
    WHERE ID = 7146 
) t
GROUP BY MSGHSTID;