Oracle 12c JSON Query Issue with Dot Notation and Double Quotes

Question

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

Following SQL works where I'm not specifying any JSON work to be done by Oracle.

select 
  m."Metadata"
from "EvMetadata" m

As you can see below, the Metadata column simply displays its content which happens to be JSON data.

However, I get error if I were to issue a json query 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?

Error is

ORA-00904: "M"."Metadata"."FileName": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 2 Column: 3

Could this be a bug with Oracle's JSON query support using the dot notation under a specific scenario where database objects are declared with double quotes? The reason I suspect that may be true is that the following equivalent query, not using the dot notation, works.

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

Answer 1

You need to have an "IS JSON" check constraint on the column for dot notation to work:

Here's an excerpt from the documentation:

Each json_key must be a valid SQL identifier, and the column must have an is json check constraint, which ensures that it contains well-formed JSON data. If either of these rules is not respected then an error is raised at query compile time. (The check constraint must be present to avoid raising an error; however, it need not be active. If you deactivate the constraint then this error is not raised.)

Here's a test example I did to verify this is how it's working:

--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 issue, its documented in Oracle Support under note 2192052.1

Basically, it's a bug whereby Dot Notation doesn't work on a column which 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'll get the error when you run:

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

the Dot notation will work.

Answer 3

You do not need quotes, this shall work:

select m.Metadata.FileName from EvMetadata m

Please refer to the example of official documentation:

SELECT po.po_document.PONumber FROM j_purchaseorder po;

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