Part 3: Parsing Complex JSON Objects with DB2 SQL

So this credit goes to Birgitta Hauser , I had e-mailed her prior to posting the last two articles asking for some help as I had spent a couple of days trying to get everything working and she provided this example which is probably the easiest to understand of all of them. I just like to post these mainly for my reference but maybe it will help someone in the future. Thanks Birgitta for always contributing to the IBMi community.

So again we are going to put our JSON string into a table like before. This is to make the SQL easier to read.

-- create, clear, insert json string into a table to hold our json information
Create Or Replace Table rthompson.t_json (jsonstring Clob(5000));
Delete from rthompson.t_json;
Insert Into rthompson.t_json
  Values
    (
'{"data":[{"order":[{"order_number":"180","order_date":"2023-01-01","orderdetail":[{"sequence":1,"product_number":"ABC","quantity":10,"options":[{"color1":"blue","color2":"white"}]},{"sequence":2,"product_number":"DEF","quantity":5,"options":[{"color1":"green","color2":"yellow"}]}]},{"order_number":"181","order_date":"2023-02-01","orderdetail":[{"sequence":1,"product_number":"RLT","quantity":10,"options":[{"color1":"cyan","color2":"black"}]},{"sequence":2,"product_number":"XYZ","quantity":5,"options":[{"color1":"purple","color2":"gray"}]}]}]}]}'
    );        

Instead of using the UNNEST table function that we built previously we will use a SQL keyword called Nested to basically unnest the other arrays.

Also she explained that you don't have to specific the path if you use the same names as in the JSON, but you can if needed and I provided one example of that for order_number. Below would be an example parsing the above JSON object and having a single row for the Order/Detail Line/Options.

Select x.*
  From Json_Table(
      (Select *
          From rthompson.t_json
          Fetch First Row Only),
      '$.data.order[*]'
      Columns(
        "order_number" Integer path '$.order_number'
        ,"order_date" Date
        ,Nested '$.orderdetail[*]' Columns(
          "sequence" Integer
          ,"product_number" Char(20)
          ,"quantity" Integer
          ,Nested '$.options[*]' Columns(
            "color1" Char(10)
            ,"color2" Char(10)
          )
        )
      )
    ) As x;        

Hope this helps someone in the future.