# 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`](https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/JSON_VALUE.html#GUID-C7F19D36-1E75-4CB2-AE67-ADFBAD23CBC2) 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`

```sql
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](https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/simple-dot-notation-access-to-json-data.html#GUID-7249417B-A337-4854-8040-192D5CEFD576). 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)
    

```sql
-- 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](https://docs.oracle.com/en/database/oracle/oracle-database/23/adjsn/simple-dot-notation-access-to-json-data.html#GUID-7249417B-A337-4854-8040-192D5CEFD576) as it covers all the features about JSON dot notation in SQL.

*Thanks to* [*Stefan Dobre*](https://twitter.com/stefan__dobre) *for pointing this feature out to me.*