Simple queries over JSON documents

In the previous blog we saw about how to store JSON data in oracle database, let’s see how to query them now.

demo@ORA12C> create table t(doc_id number,
  2     doc_name varchar2(1000) ,
  3     constraint is_valid_json check(doc_name IS JSON) );

Table created.

demo@ORA12C> insert into t(doc_id,doc_name)
  2     values(1, '{
  3     "firstName" : "Tom",
  4     "ColorName": "black",
  5     "rgb": [0,0,0],
  6     "hex": "#000000"
  7     }');

1 row created.

demo@ORA12C> insert into t(doc_id,doc_name)
  2     values(2, '{
  3     "firstName" : "Kyte",
  4     "ColorName": "White",
  5     "rgb": [255,255,255],
  6     "hex": "#FFFFFF"
  7     }');

1 row created.

demo@ORA12C> commit;

Commit complete.


Now that I have some JSON in database, let’s go ahead and retrieve some of the data via SQL. With the added JSON support also comes an enhanced SQL engine that allows you to use a simple-dot-notation (SDN) syntax on your JSON data (i.e. you can write SQL queries that contain something like <table_alias>.<json_column_name>.<json_key>.<json_key>), but be careful, the SDN syntax is case sensitive! 

The query to retrieve the “firstname” from JSON document using SDN syntax will be like this.

demo@ORA12C> select t1.doc_name.firstName from t t1;

FIRSTNAME
------------------------------------------------------
Tom
Kyte

2 rows selected.


If the JSON key from SDN syntax doesn’t matches with JSON keys in the document stored in the column, then NULL values would be returned. (Say instead of “firstName” if we use “firstname” in the SDN query, then NULL values will be returned)

demo@ORA12C> select t1.doc_name.firstname from t t1;

FIRSTNAME
------------------------------------------------------



2 rows selected.



So what happens if we use a key name that doesn’t in the data, (let’s say ‘description’) then we get NULL values back rather than an error message. This is because in many JSON use cases there are optional fields which do not exist in every instance.


demo@ORA12C> select t1.doc_name.description from t t1;

DESCRIPTION
----------------------------------------------------------



2 rows selected.


This SDN allows us to select a complex value (i.e. array or objects)


demo@ORA12C> select t1.doc_name.rgb from t t1;

RGB
-------------------------------------------------
[0,0,0]
[255,255,255]

2 rows selected.


This SDN syntax works well for simple queries like the one illustrated above, but it comes with some limitation. That is it requires the presence of check constraint (either in enabled/disabled status) that marks the columns as a JSON column, in case of missing constraint over JSON column would return error message.


demo@ORA12C> select t1.doc_name.firstName from t t1;

FIRSTNAME
------------------------------------------------------
Tom
Kyte

2 rows selected.

demo@ORA12C> alter table t disable constraint is_valid_json;

Table altered.

demo@ORA12C> select t1.doc_name.firstName from t t1;

FIRSTNAME
-------------------------------------------------------------
Tom
Kyte

2 rows selected.

demo@ORA12C> alter table t drop constraint is_valid_json;

Table altered.

demo@ORA12C> select t1.doc_name.firstName from t t1;
select t1.doc_name.firstName from t t1
       *
ERROR at line 1:
ORA-00904: "T1"."DOC_NAME"."FIRSTNAME": invalid identifier


demo@ORA12C>