DB2 Building json using JSON_ARRAY

Question

I am using DB2LUW 11.5. I am building a JSON under usage of the below tables and want an output like this I tried many things but I come not up to a solution.

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

ID	NAME
1	a

Table_B

ID	ID_A	SIZE
1	1	5
2	1	10
3	1	15

Table_C

ID	ID_A	SIZE
1	1	100
2	1	200

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

I get a mulitplication of the results

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

Look at the updated answer here.

Mark Barinstein
– Mark Barinstein

2023-02-02 18:58:54 +00:00
Commented Feb 2, 2023 at 18:58 — Mark Barinstein
– Mark Barinstein, Commented Feb 2, 2023 at 18:58

nfgl · Accepted Answer · 2023-02-03 09:23:15Z

0

You have to build arrays from table B and C in different queries. By the way, since your version is 11.5 you will maybe sometime have JSON_ARRAYAGG available

So you can write :

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)
)
select
 JSON_OBJECT 
  (
    'ID' VALUE A.ID,
    'NAME' VALUE A.NAME,
    'B_OBJECTS' VALUE (
      select json_arrayagg(
        JSON_OBJECT('ID' VALUE b.ID, 'SIZE' VALUE b.SIZE)
      ) from table_b b where b.id_a = a.id
    ) format json,
    'C_OBJECTS' VALUE (
      select json_arrayagg(
        JSON_OBJECT('ID' VALUE C.ID, 'SIZE' VALUE C.SIZE)
      ) from table_c c where c.id_a = a.id) format json
    absent on null
  )
from table_a a

gives

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

edited Feb 3, 2023 at 9:23

answered Feb 2, 2023 at 17:04

nfgl

3,2109 silver badges19 bronze badges

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

3 Comments

Mark Barinstein Over a year ago

Despite the fact the json_arrayagg is mentioned in the documentation, it doesn't work at least on my 11.5.7. One may check it on their latest 11.5.8: SELECT JSON_ARRARYAGG (JSON_OBJECT ('ID' VALUE 1)) FROM SYSIBM.SYSDUMMY1.

nfgl Over a year ago

@MarkBarinstein Well... did not have access to newer than 11.1 at the time, but tested it with DB2 for IBM i...

DB2fan Over a year ago

Yeah with 11.5.7 no support.

Collectives™ on Stack Overflow

DB2 Building json using JSON_ARRAY

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related