Db2 Developer-C 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH TAB (DOC_ID, DOC) AS 
(
VALUES
(
1,
'{"id"       : 103,                                       
  "orderDate": "2014-06-20",                       
  "items": {                                        
             "item": [ { "partNum": "872-AA",                         
                         "productName": "Lawnmower",                   
                         "quantity": 1,                                
                         "USPrice": 749.99                            
                       },                                              
                       { "partNum": "837-CM",                         
                         "productName": "Digital Camera",           
                         "quantity": 2,                                
                         "USPrice": 199.99                            
                       }                                                
                     ]                                               
            }                                                  
 }'
)
)
-- get a JSON array only for each record
, ITEMS_ARRAY (DOC_ID, ITEMS) AS 
(
  SELECT DOC_ID, JSON_OBJECT(KEY 'items' VALUE JSON_QUERY(DOC, '$.items.item') FORMAT JSON)
  FROM TAB
)
-- Use RCTE to unnest it
, ITEMS (DOC_ID, INDEX, ITEM) AS
(
  SELECT DOC_ID, 0, JSON_QUERY(ITEMS, '$.items[0]')
  FROM ITEMS_ARRAY
  WHERE JSON_EXISTS(ITEMS, '$.items[0]')
    UNION ALL
  SELECT I.DOC_ID, I.INDEX+1, JSON_QUERY(A.ITEMS, '$.items['|| TRIM(I.INDEX+1) ||']')
  FROM ITEMS I, ITEMS_ARRAY A
  WHERE I.DOC_ID = A.DOC_ID AND JSON_EXISTS(A.ITEMS, '$.items['|| TRIM(I.INDEX+1) ||']')
)
SELECT D.*, IT.*
--, I.*
FROM TAB T
JOIN ITEMS I ON I.DOC_ID = T.DOC_ID
-- array element to row
CROSS JOIN JSON_TABLE
  (
    I.ITEM, 'strict $' COLUMNS
    (
        PARTNUM      VARCHAR(20) PATH '$.partNum'
      , PRODCUCTNAME VARCHAR(20) PATH '$.productName' 
      , QUANTITY     INT         PATH '$.quantity'
      , USPRICE      DECFLOAT    PATH '$.USPrice'
    ) ERROR ON ERROR
  ) IT
-- other elements of original JSON to row
CROSS JOIN JSON_TABLE
  (
    T.DOC, 'strict $' COLUMNS
    (
        ID        INT  PATH '$.id'
      , ORDERDATE DATE PATH '$.orderDate'
    ) ERROR ON ERROR
  ) D;

ID	ORDERDATE	PARTNUM	PRODCUCTNAME	QUANTITY	USPRICE
103	2014-06-20	872-AA	Lawnmower	1	749.99000000000001
103	2014-06-20	837-CM	Digital Camera	2	199.99000000000001