Oracle 18c

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
CREATE TABLE jsondocuments (data CLOB CHECK (data iS JSON));
INSERT INTO jsondocuments (data)
VALUES ('[[{"PONumber":1,"ItemNumber":1,"Part":{"Description":"Tora! Tora! Tora!","UnitPrice":19.95,"UPCCode":24543013174},"Quantity":2.0},{"ItemNumber":2,"Part":{"Description":"The Beastmaster","UnitPrice":19.95,"UPCCode":13131201598},"Quantity":4.0},{"ItemNumber":3,"Part":{"Description":"Heavy Traffic","UnitPrice":19.95,"UPCCode":27616852854},"Quantity":6.0}]]')
1 rows affected

SELECT jt.*
FROM   jsondocuments j
       CROSS JOIN JSON_TABLE(
         j.data,
         '$[*][*]'
         COLUMNS (
           ponumber    NUMBER        PATH '$.PONumber',
           itemnumber  NUMBER        PATH '$.ItemNumber',
           description VARCHAR2(200) PATH '$.Part.Description',
           unitprice   VARCHAR2(200) PATH '$.Part.UnitPrice',
           upccode     VARCHAR2(200) PATH '$.Part.UPCCode',
           quantity    NUMBER        PATH '$.Quantity'
         )
       ) jt


PONUMBER
ITEMNUMBER
DESCRIPTION
UNITPRICE
UPCCODE
QUANTITY


1
1
Tora! Tora! Tora!
19.95
24543013174
2

null
2
The Beastmaster
19.95
13131201598
4

null
3
Heavy Traffic
19.95
27616852854
6


SELECT jt.*
FROM   jsondocuments j
       CROSS JOIN JSON_TABLE(
         j.data,
         '$[*]'
         COLUMNS (
           ponumber    NUMBER        PATH '$[0].PONumber',
           NESTED PATH '$[*]' COLUMNS (
             itemnumber  NUMBER        PATH '$.ItemNumber',
             description VARCHAR2(200) PATH '$.Part.Description',
             unitprice   VARCHAR2(200) PATH '$.Part.UnitPrice',
             upccode     VARCHAR2(200) PATH '$.Part.UPCCode',
             quantity    NUMBER        PATH '$.Quantity'
           )
         )
       ) jt


PONUMBER
ITEMNUMBER
DESCRIPTION
UNITPRICE
UPCCODE
QUANTITY


1
1
Tora! Tora! Tora!
19.95
24543013174
2

1
2
The Beastmaster
19.95
13131201598
4

1
3
Heavy Traffic
19.95
27616852854
6

PONUMBER	ITEMNUMBER	DESCRIPTION	UNITPRICE	UPCCODE	QUANTITY
1	1	Tora! Tora! Tora!	19.95	24543013174	2
null	2	The Beastmaster	19.95	13131201598	4
null	3	Heavy Traffic	19.95	27616852854	6