主页 / dba / 问题 / 268557
Accepted
a_horse_with_no_name
Asked: 2020-06-06 00:04:53 +0800 CST

SQL/JSON 路径规范是否允许访问过滤器表达式结果的属性

  • 772

我注意到 Oracle 和 Postgres 对 SQL/JSON 路径表达式的评估存在差异，我想知道哪个是正确的。

考虑以下 JSON 结构：

{
  "key1": {"id":1000, "price": 42},
  "key2": {"id":2000, "price": 3.14},
  "key3": {"id":3000, "price": 2.7}
}

要获取 ID = 1000 的产品价格，Postgres 允许我使用以下 JSON 路径表达式：

jsonb_path_query(doc, '$.* ? (@.id == 1000).price')

但是，当使用 Oracle 的json_value()（或json_query()- 没关系）

json_value(doc, '$.* ? (@.id == 1000).price')

我收到以下错误（使用 Oracle 18c）：

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

Oracle 18c 中有解决方法：

json_value(json_query(doc, '$.* ? (@.id == 1000)'), '$.price')

所以我的问题是：

SQL 标准是否允许在 Postgres 的实现中进行属性访问？
或者这根本没有在标准中指定并标记为“依赖于实现”？

（鉴于 Postgres 尽可能坚持标准的记录，我怀疑 Postgres 是正确的，而 Oracle 没有实施完整的标准）

oracle postgresql

1 个回答

  1. Best Answer

    我不知道标准，但它在 19c 中工作得很好。

    脚本：

    [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

相关问题