The following statement works in Oracle Database 23ai but produces a syntax error in the VS Code extension 24.3.1.
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 json_transform(
po_document,
set '$new' = json('[ "+41527772211", "+41791234578" ]'),
prepend '$.ShippingInstructions.Phone' = path '$new[*]'
ignore on missing
replace on mismatch
ignore on null
error on empty
) as transformed
from j_purchaseorder;
Here's the screenshot showing the complete error message:
