DB2 LUW 11.5中去除JSON_ARRAY重复元素及多表关联生成JSON

一、初始需求：B_OBJECTS中ID仅保留一条记录 #

当前生成的JSON输出 #

{        
    "ID": 1,        
    "NAME": "a",        
    "B_OBJECTS": [{                
            "ID": 1,                
            "SIZE": 10            
        }, {                
            "ID": 1,                
            "SIZE": 20            
        }        
    ]    
}

期望输出（B_OBJECTS中ID仅出现一次） #

{        
    "ID": 1,        
    "NAME": "a",        
    "B_OBJECTS": [{                
            "ID": 1,                
            "SIZE": 10            
        }        
    ]    
}

当前使用的查询语句 #

WITH TABLE_A(ID,NAME) AS (        
    VALUES (1, 'a')),
TABLE_B(ID, ID_A, SIZE) AS (        
    VALUES (1, 1, 10), (1, 1, 20)),
JSON_STEP_1 AS (        
    SELECT A.ID AS A_ID, A.NAME AS A_NAME, B.ID AS B_ID,
    JSON_OBJECT('ID' VALUE B.ID, 'SIZE' VALUE B.SIZE) B_JSON
    FROM TABLE_A A
    JOIN TABLE_B B ON B.ID_A = A.ID              
    GROUP BY A.ID, A.NAME, B.ID, B.SIZE),
JSON_STEP_2 AS ( SELECT JSON_OBJECT ('ID' VALUE A_ID,                    
    'NAME' VALUE A_NAME,                    
    'B_OBJECTS' VALUE JSON_ARRAY (LISTAGG(B_JSON, ', ') WITHIN GROUP (ORDER BY B_ID) FORMAT JSON) FORMAT JSON                    
    ) JSON_OBJS FROM JSON_STEP_1 GROUP BY A_ID, A_NAME) 
SELECT JSON_ARRAY (SELECT JSON_OBJS FROM JSON_STEP_2 FORMAT JSON) FROM SYSIBM.SYSDUMMY1;

二、添加TABLE_C后的需求 #

更新后的查询语句 #

WITH   TABLE_A(ID,NAME) AS (        
    VALUES (1, 'a')), 
TABLE_B(ID, ID_A, SIZE) AS (        
    VALUES (1, 1, 10), (1, 1, 20), (2, 1, 10), (2, 1, 20)), 
TABLE_C(ID, ID_A, SIZE) AS(        
    VALUES (1, 1, 5), (2,1,10), (3,1,15)), 
JSON_STEP_1 AS (  
    SELECT A_ID, A_NAME, B_ID  
    , JSON_OBJECT('ID' VALUE B_ID, 'SIZE' VALUE B_SIZE) B_JSON  
    , JSON_OBJECT('ID' VALUE C_ID, 'SIZE' VALUE C_SIZE) C_JSON  
    FROM  (        
        SELECT           
            A.ID AS A_ID, A.NAME AS A_NAME, B.ID AS B_ID, B.SIZE AS B_SIZE, C.ID AS C_ID, C.SIZE AS C_SIZE        
        , ROW_NUMBER () OVER (PARTITION BY B.ID, B.ID_A, B.SIZE) AS RN_        
        , ROW_NUMBER () OVER (PARTITION BY C.ID, C.ID_A, C.SIZE) AS RN1_                
        FROM TABLE_A A        
        JOIN TABLE_B B ON B.ID_A = A.ID              
        JOIN TABLE_C C ON C.ID_A = A.ID     
    )      
    WHERE RN_ = 1 AND RN1_ = 1   
    GROUP BY A_ID, A_NAME, B_ID, B_SIZE, B_ID, B_SIZE, C_ID, C_SIZE), 
JSON_STEP_2 AS ( SELECT JSON_OBJECT   
    (    
        'ID' VALUE A_ID,    
        'NAME' VALUE A_NAME,    
        'B_OBJECTS' VALUE JSON_ARRAY (LISTAGG(B_JSON, ', ') WITHIN GROUP (ORDER BY B_ID) FORMAT JSON) FORMAT JSON,    
        'C_OBJECTS' VALUE JSON_ARRAY (LISTAGG(C_JSON, ', ') WITHIN GROUP (ORDER BY B_ID) FORMAT JSON) FORMAT JSON  
    ) JSON_OBJS FROM JSON_STEP_1 GROUP BY A_ID, A_NAME) 
SELECT JSON_ARRAY (SELECT JSON_OBJS FROM JSON_STEP_2 FORMAT JSON) FROM SYSIBM.SYSDUMMY1

期望输出 #

{        
    "ID": 1,        
    "NAME": "a",        
    "B_OBJECTS": [{                
            "ID": 1,                
            "SIZE": 10            
        },            
        {                
            "ID": 1,                
            "SIZE": 20            
        },            
        {                
            "ID": 2,                
            "SIZE": 10            
        },            
        {                
            "ID": 2,                
            "SIZE": 20            
        }        
    ],        
    "C_OBJECTS": [{                
            "ID": 1,                
            "SIZE": 5            
        },            
        {                
            "ID": 2,                
            "SIZE": 10            
        },            
        {                
            "ID": 3,                
            "SIZE": 15            
        }        
    ]    
}

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