According to the documentation of JSON_TABLE
the following statement should work:
with
j_purchaseorder as (
select json(
'{
"PONumber": 1600,
"Reference": "ABULL-20140421",
"Requestor": "Alexis Bull",
"User": "ABULL",
"CostCenter": "A50",
"ShippingInstructions": {
"name": "Alexis Bull",
"Address": {
"street": "200 Sporting Green",
"city": "South San Francisco",
"state": "CA",
"zipCode": 99236,
"country": "United States of America"
},
"Phone": [
{ "type": "Office", "number": "909-555-7307" },
{ "type": "Mobile", "number": "415-555-1234" }
]
},
"Special Instructions": null,
"AllowPartialShipment": true,
"LineItems": [
{
"ItemNumber": 1,
"Part": { "Description": "One Magic Christmas", "UnitPrice": 19.95, "UPCCode": 13131092899 },
"Quantity": 9.0
},
{
"ItemNumber": 2,
"Part": { "Description": "Lethal Weapon", "UnitPrice": 19.95, "UPCCode": 85391628927 },
"Quantity": 5.0
}
]
}') as po_document
)
select jt.*
from j_purchaseorder,
json_table(
po_document, '$'
error on error
type strict -- does not work in 23.5, 23.6. bug in doc or implementation
error on empty
columns(
requestor varchar2(32) path '$.Requestor',
nested path '$.ShippingInstructions.Phone[*]'
columns (
phone_type varchar2(32) path '$.type',
phone_num varchar2(20) path '$.number'
)
)
)
as jt;
But it produces a ORA-02000: missing COLUMNS keyword.
This is either a documentation bug or an implementation bug.
The statement works when you remove the line starting with type strict -- does not work.