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 |