I noticed a difference in the evaluation of a SQL/JSON path expression between Oracle and Postgres and I wonder which one is correct.
Consider the following JSON structure:
{
"key1": {"id":1000, "price": 42},
"key2": {"id":2000, "price": 3.14},
"key3": {"id":3000, "price": 2.7}
}
To get the price of the product with ID = 1000, Postgres allows me to use the following JSON path expression:
jsonb_path_query(doc, '$.* ? (@.id == 1000).price')
However, when using Oracle's json_value()
(or json_query()
– doesn't matter)
json_value(doc, '$.* ? (@.id == 1000).price')
I get the following error (using Oracle 18c):
ORA-40597: JSON path expression syntax error ('$.* ? (@.id == 1000).price') JZN-00209: Unexpected characters after end of path at position 21
There is workaround in Oracle 18c:
json_value(json_query(doc, '$.* ? (@.id == 1000)'), '$.price')
So my question is:
Does the SQL standard allow the property access as done in Postgres' implementation?
Or is this simply not specified in the standard and marked as "implementation dependent"?
(Given Postgres' track record with sticking to the standard as much as possible, I suspect Postgres is correct and Oracle is not implementing the full standard)
Best Answer
I do not know about the standard, but it works just fine in 19c.
Script:
18c (18.10):
19c (19.7):