Dot Notation for JSON in Oracle SQL

When dealing with Oracle SQL statements and the need to parse JSON arises, you've likely encountered the json_value function. However, Oracle offers a more straightforward approach through simple dot-notation access to JSON data. This feature can simplify your SQL coding process. Below is an example of a basic query using json_value

create table demo_json (
    id number,
    json_data varchar2(4000)
);

insert into demo_json(id, json_data)
values(1, '{
    "name": "Martin",
    "company": "Oracle",
    "colors": ["red", "white", "blue"],
    "age": 25,
    "index": "demo_index"
}');
commit;

select
    json_value(d.json_data, '$.name') name,
    json_value(d.json_data, '$.colors[2]') third_color,
    json_value(d.json_data, '$.age.number()') age
from demo_json d
;

NAME   THIRD_COLOR AGE 
------ ----------- --- 
Martin blue         25

Oracle has the ability to use simple dot-notation access to json data. They're two requirements for this to work:

• The column type must either be type json or it must have an is json check constraint.

• Must alias the table when selecting (in the demo using d as table alias name)

-- Highlight that a check constraint is 
select d.json_data.name
from demo_json d
;


00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 1 Column: 6
;

-- Add check constraint
alter table demo_json add constraint demo_json_ck1 check (json_data is json);

select 
    d.json_data.name,
    d.json_data.colors[2] third_color,
    d.json_data.age.number() age
from demo_json d
;

NAME   THIRD_COLOR AGE 
------ ----------- --- 
Martin blue         25

It's recommended to read the documentation as it covers all the features about JSON dot notation in SQL.

Thanks to Stefan Dobre for pointing this feature out to me.