DB2 LUW 11.5使用JSON_ARRAY构建嵌套JSON重复数据问题求助

期望输出JSON #

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

数据表结构 #

Table_A #

Table_B #

Table_C #

错误SQL及问题现象 #

用户编写的SQL如下：

WITH 
  TABLE_A(ID,NAME) AS 
(
        VALUES (1, 'a')
)
, TABLE_B(ID, ID_A, SIZE) AS 
(
        VALUES (1, 1, 5), (2, 1, 10), (3, 1, 15)
), TABLE_C(ID, ID_A, SIZE) AS
(
        VALUES (1, 1, 100), (2,1, 200)
)
, JSON_STEP_1 AS 
(
  SELECT A_ID, A_NAME, B_ID, C_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
        FROM TABLE_A A
        JOIN TABLE_B B ON B.ID_A = A.ID      
        JOIN TABLE_C C ON C.ID_A = A.ID     
   )     
   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 C_ID) FORMAT JSON) FORMAT JSON
  ) JSON_OBJS
 FROM JSON_STEP_1
 GROUP BY A_ID, A_NAME
) 
SELECT * FROM JSON_STEP_2

执行后出现数据重复，每个B_OBJECTS元素重复2次，C_OBJECTS元素重复3次，输出如下：

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

问题根源 #

直接将Table_B和Table_C同时与Table_A JOIN会产生笛卡尔积：Table_B有3条匹配记录，Table_C有2条匹配记录，最终生成3×2=6条中间记录。后续LISTAGG会把这6条记录中的B_JSON和C_JSON分别累加，导致每个B元素重复2次（对应Table_C的2条记录），每个C元素重复3次（对应Table_B的3条记录）。

解决方案 #

先分别对Table_B和Table_C按ID_A聚合生成JSON数组，再将这两个聚合结果与Table_A关联，避免笛卡尔积。

修正后的SQL：

WITH 
  TABLE_A(ID,NAME) AS 
(
        VALUES (1, 'a')
)
, TABLE_B(ID, ID_A, SIZE) AS 
(
        VALUES (1, 1, 5), (2, 1, 10), (3, 1, 15)
), TABLE_C(ID, ID_A, SIZE) AS
(
        VALUES (1, 1, 100), (2,1, 200)
)
-- 先聚合Table_B为JSON数组
, AGG_B AS (
    SELECT 
        ID_A,
        JSON_ARRAYAGG(JSON_OBJECT('ID' VALUE ID, 'SIZE' VALUE SIZE) ORDER BY ID) AS B_OBJECTS
    FROM TABLE_B
    GROUP BY ID_A
)
-- 先聚合Table_C为JSON数组
, AGG_C AS (
    SELECT 
        ID_A,
        JSON_ARRAYAGG(JSON_OBJECT('ID' VALUE ID, 'SIZE' VALUE SIZE) ORDER BY ID) AS C_OBJECTS
    FROM TABLE_C
    GROUP BY ID_A
)
-- 关联主表和两个聚合结果
SELECT 
    JSON_OBJECT(
        'ID' VALUE A.ID,
        'NAME' VALUE A.NAME,
        'B_OBJECTS' VALUE B.B_OBJECTS FORMAT JSON,
        'C_OBJECTS' VALUE C.C_OBJECTS FORMAT JSON
    ) AS JSON_OBJS
FROM TABLE_A A
LEFT JOIN AGG_B B ON A.ID = B.ID_A
LEFT JOIN AGG_C C ON A.ID = C.ID_A

执行该SQL后，将得到符合预期的无重复嵌套JSON。

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