DB2 Avoid Duplicates in JSON_ARRAY

Question

I am using DB2LUW 11.5. I build a json and gets an output like this

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

But I want the id from B_OBJECTS listed only once.

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

Here is my query...

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;

I just updated the query with additional table 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

The output should be like

{
        "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
            }
        ]
    }

Db2 LUW 11.5 or what platform and version? What is the sample table / data? — data_henrik
– data_henrik, Commented Jan 31, 2023 at 13:38
yeah the platform is DB2 LUW 11.5, the above json is the output of the query — DB2fan
– DB2fan, Commented Jan 31, 2023 at 13:43
What is the input...? See the requirement for something to reproduce. Or do you want a generic answer "try to improve the query"? — data_henrik
– data_henrik, Commented Jan 31, 2023 at 15:35

Mark Barinstein · Accepted Answer · 2023-02-02 17:34:15Z

0

WITH 
  TABLE_A (ID,NAME) AS 
(
  VALUES (1, 'a')
)
, TABLE_B (ID, ID_A, SIZE) AS 
(
  VALUES (1, 1, 10), (1, 1, 10), (2, 1, 10), (2, 1, 20)
)
, TABLE_C (ID, ID_A, SIZE) AS
(
  VALUES (1, 1, 5), (2, 1, 10), (3, 1, 15)
)
SELECT 
JSON_OBJECT 
(
    'ID' VALUE A.ID
  , 'NAME' VALUE A.NAME
  , 'B_OBJECTS' VALUE 
    JSON_ARRAY 
    (
      (
        SELECT JSON_OBJECT ('ID' VALUE ID, 'SIZE' VALUE SIZE FORMAT JSON)
        FROM TABLE (SELECT DISTINCT ID, SIZE FROM TABLE_B B WHERE B.ID_A = A.ID ORDER BY ID)
      ) 
      FORMAT JSON
    ) FORMAT JSON
  , 'C_OBJECTS' VALUE 
    JSON_ARRAY 
    (
      (
        SELECT JSON_OBJECT ('ID' VALUE ID, 'SIZE' VALUE SIZE FORMAT JSON)
        FROM TABLE (SELECT DISTINCT ID, SIZE FROM TABLE_C C WHERE C.ID_A = A.ID ORDER BY ID)
      ) 
      FORMAT JSON
    ) FORMAT JSON
) JSON_OBJ
FROM TABLE_A A

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

edited Feb 2, 2023 at 17:34

answered Feb 1, 2023 at 17:13

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

DB2fan Over a year ago

Yeah it works fine, many thx. I just updated my query for an additional table TABLE_C, but I got not the expected output. I am not sure if I can expect such like an output within DB2.

Mark Barinstein Over a year ago

Is the goal to eliminate from the output duplicates by (id, id_a) from both B and C? If yes then why do we see them from B in the new output?

DB2fan Over a year ago

Yeah normally the main goal should be that each entry is listed only once wit (id, id_a, size) for both tables B and C, but I recognized it after adding the thirth table.

DB2fan Over a year ago

Is it possible to eliminate duplicates at JSON_STEP_2, especially here

'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

DB2fan Over a year ago

I just created another issue and let it solved by your solution. stackoverflow.com/questions/75326106/…

|

Collectives™ on Stack Overflow

DB2 Avoid Duplicates in JSON_ARRAY

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related