首页 文章

带有点表示法和双引号的Oracle 12c JSON查询问题

提问于
浏览 次
7

我有一个表“EvMetadata”，其中“Metadata”列的检查约束为“IS JSON” . 请注意，表及其列是按设计使用DOUBLE QUOTES创建的 .

以下SQL工作，我没有指定任何由Oracle完成的JSON工作 .

select 
  m."Metadata"
from "EvMetadata" m

如下所示，元数据列只显示其恰好是JSON数据的内容 .

Showing content of "Metadata" column which is JSON

但是，如果我按如下方式发出json查询，则会出错 .

select 
  m."Metadata"."FileName"
from "EvMetadata" m

我刚用点符号添加了“FileName” . 如上所示，“FileName”是一个有效的json字段 . 那么为什么会出错呢？

错误是

ORA-00904：“M” . “元数据” . “FileName”：无效标识符00904. 00000 - “％s：无效标识符”*原因：*操作：行错误：2列：3

在使用双引号声明数据库对象的特定场景下使用点表示法时，这可能是Oracle JSON查询支持的错误吗？我怀疑可能是真的原因是以下等效查询，不使用点符号，有效 .

select 
  JSON_VALUE(m."Metadata", '$.FileName')
from "EvMetadata" m
json oracle syntax oracle12c

3 回答

  • 4

    您需要在列上使用“IS JSON”检查约束才能使点符号起作用：

    以下是文档的摘录：

    每个json_key必须是有效的SQL标识符，并且该列必须具有is json检查约束，以确保它包含格式正确的JSON数据 . 如果不遵守这些规则中的任何一个，则在查询编译时会引发错误 . （必须存在检查约束以避免引发错误;但是，它不需要处于活动状态 . 如果停用约束，则不会引发此错误 . ）

    这是我用来验证它是如何工作的测试示例：

    --create a table to put stuff in
create table foo (
 json varchar2(4000)
);
--------------------------------
Table FOO created.

--insert test value
insert into foo(json) values('{"attr1":5,"attr2":"yes"}');
commit;
--------------------------------
1 row inserted.
Commit complete.


--try some selects
--no table alias, no constraint, borked
select json.attr1 from foo;
--------------------------------
Error starting at line : 12 in command -
select json.attr1 from foo
Error at Command Line : 12 Column : 8
Error report -
SQL Error: ORA-00904: "JSON"."ATTR1": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:


--with table alias, no constraint, borked
select a.json.attr1 from foo a;
--------------------------------
Error starting at line : 15 in command -
select a.json.attr1 from foo a
Error at Command Line : 15 Column : 8
Error report -
SQL Error: ORA-00904: "A"."JSON"."ATTR1": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:


--add our constraint
alter table foo add constraint json_isjson check (json is json);
--------------------------------
Table FOO altered.

--no table alias, with constraint, borked
select json.attr1 from foo;
--------------------------------
Error starting at line : 21 in command -
select json.attr1 from foo
Error at Command Line : 21 Column : 8
Error report -
SQL Error: ORA-00904: "JSON"."ATTR1": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:


--table alias and constraint, works!
select a.json.attr1 from foo a;
--------------------------------
ATTR1                                                                          
--------------------------------------------------------------------------------
5
    回复于
  • -1

    如果其他人遇到此问题，请在注释2192052.1中记录在Oracle Support中

    基本上，这是一个错误，因此Dot Notation不会对使用NOT NULL约束创建的列起作用，即

    如果你这样做：

    CREATE TABLE foo.bar (id NUMBER NOT NULL, json_doc CLOB NOT NULL CHECK (json_doc IS JSON));

    你运行时会得到错误：

    SELECT a.json_doc.elementName FROM foo.bar a;

    但如果你这样做：

    CREATE TABLE foo.bar (id NUMBER NOT NULL, json_doc CLOB CHECK (json_doc IS JSON));
ALTER TABLE bar MODIFY (json_doc NOT NULL);

    点符号将起作用 .

    回复于
  • 2

    你不需要引号，这应该工作：

    select m.Metadata.FileName from EvMetadata m

    请参考官方documentation的例子：

    SELECT po.po_document.PONumber FROM j_purchaseorder po; SELECT json_value（po_document，'$ .PONumber'）FROM j_purchaseorder;

    回复于

相关问题