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

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 increate table foo ( json varchar2(4000));--------------------------------Table FOO created.--insert test valueinsert into foo(json) values('{"attr1":5,"attr2":"yes"}');commit;--------------------------------1 row inserted.Commit complete.--try some selects--no table alias, no constraint, borkedselect json.attr1 from foo;--------------------------------Error starting at line : 12 in command -select json.attr1 from fooError at Command Line : 12 Column : 8Error report -SQL Error: ORA-00904: "JSON"."ATTR1": invalid identifier00904. 00000 -  "%s: invalid identifier"*Cause:    *Action:--with table alias, no constraint, borkedselect a.json.attr1 from foo a;--------------------------------Error starting at line : 15 in command -select a.json.attr1 from foo aError at Command Line : 15 Column : 8Error report -SQL Error: ORA-00904: "A"."JSON"."ATTR1": invalid identifier00904. 00000 -  "%s: invalid identifier"*Cause:    *Action:--add our constraintalter table foo add constraint json_isjson check (json is json);--------------------------------Table FOO altered.--no table alias, with constraint, borkedselect json.attr1 from foo;--------------------------------Error starting at line : 21 in command -select json.attr1 from fooError at Command Line : 21 Column : 8Error report -SQL Error: ORA-00904: "JSON"."ATTR1": invalid identifier00904. 00000 -  "%s: invalid identifier"*Cause:    *Action:--table alias and constraint, works!select a.json.attr1 from foo a;--------------------------------ATTR1                                                                          --------------------------------------------------------------------------------5                                                                               

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.

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;