Posted by What better way to start a new week than with a cheesy title to my blog post! 
But there is method to my unstructured madness today as we talk about the TREAT function, available from 18C for JSON data. As many of us will already know it is possible to dive into JSON data and extract information from its components as if they were relational columns using the very cool dot notation
In order to do so, we need to be a little generous to the database by letting it know that the contents of a unstructured column such as a CLOB is truly correct JSON data.
I will start with a table with a simple CLOB which does indeed contain valid JSON but I have not let the database know about that fact:
SQL> create table my_json_table
2 (
3 id number(10) not null constraint my_pk primary key,
4 json_data clob
5 );
Table created.
SQL>
SQL> insert into my_json_table (id, json_data) values (1,
2 '{"fields": {"field1":{ "name": "field1","label": "My Field 1","value": "ABCD"},
3 "field2":{ "name": "field2","label": "My Field 2","value": "XYZ"},
4 "field3":{ "name": "field3","label": "My Field 3","value": "Y"},"field4":{ "name":
5 "field4","label": "My Field 4","value": ""},"field5":{ "name":
6 "field5","label": "My Field 5","value": ""},"field6":{ "name":
7 "field6","label": "My Field 6","value": ""},"field7":{ "name":
8 "field7","label": "My Field 7","value": ""},}}');
1 row created.
SQL>
SQL> insert into my_json_table (id, json_data) values (2,
2 '{"fields": {"field1":{ "name": "field1","label": "My Field 1","value": ""},
3 "field2":{ "name": "field2","label": "My Field 2","value": "XYZ"},
4 "field3":{ "name": "field3","label": "My Field 3","value": "Y"},
5 "field4":{ "name": "field4","label": "My Field 4","value": "QWERTY"},
6 "field5":{ "name": "field5","label": "My Field 5","value": ""},
7 "field6":{ "name": "field6","label": "My Field 6","value": ""},
8 "field7":{ "name": "field7","label": "My Field 7","value": ""},}}');
1 row created.
The moment I try to use the dot notation I get an error because the database can’t really guarantee that this is JSON.
SQL> select * from my_json_table m where m.json_data.fields.field1.value='ABCD';
select * from my_json_table m where m.json_data.fields.field1.value='ABCD'
*
ERROR at line 1:
ORA-00904: "M"."JSON_DATA"."FIELDS"."FIELD1"."VALUE": invalid identifier
The simple step of adding a CHECK constraint to ensure that only in JSON is allowed means that of the coolness of the dot notation becomes available to me:
SQL> alter table my_json_table modify json_data check (json_data is json);
Table altered.
SQL>
SQL> select * from my_json_table m where m.json_data.fields.field1.value='ABCD';
ID JSON_DATA
---------- --------------------------------------------------------------------------------
1 {"fields": {"field1":{ "name": "field1","label": "My Field 1","value": "ABCD"},
However, as I discovered when helping someone on StackOverflow recently sometimes the check constraint is not enough. Check constraints can only be applied to a table but sometimes we want to expose our JSON data via a view.
Here is a trivial view that simply duplicates the data from my table and look what happens when I try to use the dot notation on that view:
SQL> create or replace view my_json_view as
2 select id, json_data
3 from my_json_table
4 union all
5 select id, json_data from my_json_table;
View created.
SQL>
SQL> select * from my_json_view j where j.json_data.fields.field1.value='ABCD';
select * from my_json_view j where j.json_data.fields.field1.value='ABCD'
*
ERROR at line 1:
ORA-00904: "J"."JSON_DATA"."FIELDS"."FIELD1"."VALUE": invalid identifier
Even though both sides of the UNION ALL are valid JSON my hypothesis is that the database does not want to incur the cost of examining all the components of the query (after all we might have hundreds of UNION ALL elements) to see if every single column Is a contains a valid JSON check constraint.
Enter the TREAT function! The TREAT function also comes in very handy for handling the situation where there is no capability of having a CHECK constraint.
By wrapping the view in another SELECT and including a TREAT function in that outer SELECT, this is all that is needed to let the database know that we can consume this data as JSON and hence get the advantages of our dot notation:
SQL> create or replace view my_json_view as
2 select m.id, treat(m.json_data as json) json_data
3 from
4 (
5 select id, json_data
6 from my_json_table
7 union all
8 select id, json_data from my_json_table
9 ) m;
View created.
SQL>
SQL> select * from my_json_view j where j.json_data.fields.field1.value='ABCD';
ID JSON_DATA
---------- --------------------------------------------------------------------------------
1 {"fields": {"field1":{ "name": "field1","label": "My Field 1","value": "ABCD"},
1 {"fields": {"field1":{ "name": "field1","label": "My Field 1","value": "ABCD"},
Check out the docs on the TREAT function and if you ever get errors using that dot notation, before you resort to falling back to the JSON_VALUE and JSON_QUERY functions, then perhaps the TREAT treatment is all you need!
Footnote: We want your JSON usage to be as seamless as possible, so we’re constantly working to improve the JSON functionality. For that reason, we logged Bug 31678629 with the intent of removing the need for TREAT in this UNION ALL case, so if this is impacting you, keep an eye on that bug for progress.
Learning is not a spectator sport 