0

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)

1 Answer 1

1

I do not know about the standard, but it works just fine in 19c.

Script:

[oracle@o73 ~]$ cat x.sql
with d as (select
'{
  "key1": {"id":1000, "price": 42},
  "key2": {"id":2000, "price": 3.14},
  "key3": {"id":3000, "price": 2.7}
}' as c1
from dual
)
select json_value(c1, '$.* ? (@.id == 1000).price') x
from d;

with d as (select
'{
  "key1": {"id":1000, "price": 42},
  "key2": {"id":2000, "price": 3.14},
  "key3": {"id":3000, "price": 2.7}
}' as c1
from dual
)
select json_value(json_query(c1, '$.* ? (@.id == 1000)'), '$.price') x
from d;

exit
[oracle@o73 ~]$

18c (18.10):

[oracle@o73 ~]$ . oraenv
ORACLE_SID = [oracle] ? RYMIN18
The Oracle base has been set to /u01/app/oracle
[oracle@o73 ~]$ sqlplus / as sysdba @x

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Jun 5 10:50:17 2020
Version 18.10.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.10.0.0.0

with d as (select
*
ERROR at line 1:
ORA-40597: JSON path expression syntax error ('$.* ? (@.id == 1000).price')
JZN-00209: Unexpected characters after end of path
at position 21



X
--------------------------------------------------------------------------------
42

Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.10.0.0.0
[oracle@o73 ~]$

19c (19.7):

[oracle@o73 ~]$ . oraenv
ORACLE_SID = [RYMIN18] ? RYMIN19
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@o73 ~]$ sqlplus / as sysdba @x

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jun 5 10:50:31 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0


X
--------------------------------------------------------------------------------
42


X
--------------------------------------------------------------------------------
42

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
[oracle@o73 ~]$
1
  • You are right about 19c - I thought I tested it there, but apparently that server wasn't upgraded yet and was still running 18.7. So it seems it's just a matter of "not supported yet".
    – user1822
    Commented Jun 5, 2020 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.