Create a nested JSON with SQL in Db2-CodePudding

I have actually a JSON extracted from a table

select  json_object ('ITEM_ID' value Trim(PRDC), 
                     'ITEM_DESC' value TRIM(DESC),
                                       ) JSONData
from my_file

I Obtein this:

[{"ITEM_ID":"PRODUCT_01","ITEM_ITDESC":"DESC_01"},
 {"ITEM_ID":"PRODUCT_02","ITEM_ITDESC":"DESC_02"},
...
]

Now I need an extraction like this...

[{"ITEM_ID":"PRODUCT_01","ITEM_ITDESC":"DESC_01", "ITEM_TIERPRICE": [
  {"QTY": 5, "PRICE": 7.0000},
  {"QTY": 10, "PRICE": 6.0000}
]},
{"ITEM_ID":"PRODUCT_02","ITEM_ITDESC":"DESC_02", "ITEM_TIERPRICE": [
  {"QTY": 5, "PRICE": 6.0000},
  {"QTY": 10, "PRICE": 5.0000}
]}
...
]

But I don't know how to obtain it. The ITEM_TIERPRICE is like

SELECT QTY, PRICE FROM PRICE_TABLE WHERE ITEM = MY_ITEM

CodePudding user response：

So you want an JSON object that includes an array of JSON objects..

Have you looked at the examples in the documentation?

This one in particular

select 
  json_object(
      'department number' value deptno
      , 'department name' value deptname
      , 'employee list' 
         value json_arrayagg(
                 json_object(
                     'last name' value lastname
                     , 'employee id' value empno
                 )
               order by lastname
          )
       )
  from dept
       left outer join emp
         on deptno = workdept
  where deptno like 'D%'
  group by deptno
           ,deptname;

Note the use of JSON_OBJECT(JSON_ARRAY(JSON_OBJECT()))