How to eliminate duplicate rows in JSON_ARRAYAGG in DB2400 or in any SQL?

user_13252239 0
Viewed 1125

SQL Example...

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;

Data Retrieved: I want only one line retrieved instead of two lines. Any idea? 

[
    {"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"}
]
db2-400 group-by sql json
2 Answers

One option uses distinct in a subquery:

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;
user_106767160

You could use LISTAGG:

SELECT '[' || listagg(DISTINCT a, ',') || ']'
FROM (VALUES('a'),('a'),('b')) t(a)

In your case:

SELECT
    ID AS HID,
    '[' || LISTAGG(    
       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;

If you intend to nest that JSON array in other JSON_OBJECT structures, don't forget the FORMAT JSON clause there

user_5217990
Related