Oracle 12c JSON Query Issue with notes and double quotes

Question

I have an "EvMetadata" table with a "Metadata" column that has a "IS JSON" control constraint. Note that the table and its columns are created using DOUBLE QUOTES by design.

After SQL works, where I do not indicate that any JSON work should be done by Oracle.

select m."Metadata" from "EvMetadata" m 

As you can see below, a metadata column simply displays its contents, which is JSON data.

However, I get an error if I had to execute a json request as follows.

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

I just added "FileName" using dot notation. As you can see above, "FileName" is a valid json field. So why the error?

Mistake

ORA-00904: "M". "Metadata." "FileName": invalid identifier 00904. 00000 - "% s: invalid identifier" * Reason: * Action: Error on line: 2 Column: 3

Could this be a mistake in supporting Oracle JSON queries using dot notation in a particular scenario where database objects are declared with double quotes? The reason I suspect may be true is because the following equivalent query works without using dot notation.

 select JSON_VALUE(m."Metadata", '$.FileName') from "EvMetadata" m 

Answer 1

You need to have a "IS JSON" check constraint in the column for dot notation:

Here is an excerpt from the documentation:

Each json_key must be a valid SQL identifier, and the column must have a json validation constraint that ensures that it contains well-formed JSON data. If any of these rules is not respected, an error occurs during query compilation. (A validation constraint must be present to avoid raising the error, however it should not be active. If you disable the restriction, this error will not be raised.)

Here is an example test I did to test how this works:

 --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 

Answer 2

In case anyone else gets this problem, its documentation in Oracle Support number 2192052.1

This is basically an error where Dot Notation does not work in a column that is created with a NOT NULL constraint, i.e.

If you do:

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

You will receive an error message at startup:

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

but if you do:

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

Longitude will work.

Answer 3

You don't need quotes, this will work:

select m.Metadata.FileName from EvMetadata m

Please refer to the official documentation example:

SELECT po.po_document.PONumber FROM j_purchaseorder po;

SELECT json_value(po_document, '$.PONumber') FROM j_purchaseorder;