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 anis 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.