具有点符号和双引号的Oracle 12c JSON查询问题
我有一个表“EvMetadata”列“元数据”，有一个检查约束“IS JSON”。 请注意，该表及其列使用双引号设计。
以下SQL在我没有指定由Oracle完成的任何JSON工作的情况下工作。
select
m."Metadata"
from "EvMetadata" m
正如您在下面看到的，元数据列仅显示其恰好是JSON数据的内容。
但是，如果我按照以下方式发布json查询，则会出错。
select
m."Metadata"."FileName"
from "EvMetadata" m
我刚刚使用点符号添加了“FileName”。 正如你在上面看到的，“FileName”是一个有效的json字段。 那么为什么错误？
错误是
ORA-00904：“M”。“元数据”。“文件名”：无效标识符00904. 00000 - “％s：无效标识符”*原因：*操作：线路错误：2列：3
这可能是Oracle的JSON查询支持在使用双引号声明数据库对象的特定情况下使用点符号的错误吗？ 我怀疑这可能是真实的原因是下面的等效查询，不使用点符号，起作用。
select
JSON_VALUE(m."Metadata", '$.FileName')
from "EvMetadata" m
您需要在该列上使用“IS JSON”检查约束来使点符号工作：
以下是文档摘录：
每个json_key必须是一个有效的SQL标识符，并且该列必须具有一个是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
如有其他人遇到此问题，请参阅注释2192052.1中的Oracle支持文档
基本上，这是一个错误，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);
点符号将起作用。
你不需要报价，这将起作用：
select m.Metadata.FileName from EvMetadata m
请参考官方文档的例子：
SELECT po.po_document.PONumber FROM j_purchaseorder po;
SELECT json_value(po_document, '$.PONumber') FROM j_purchaseorder;
上一篇: Oracle 12c JSON Query Issue with Dot Notation and Double Quotes
下一篇: Bad Access crash while converting to NSMutableattributed string