Create a Table to Store JSON
create table departments_json (
department_id integer not null primary key,
department_data blob not null
)
Table created.
Add an IS JSON constraint to the table
alter table departments_json
add constraint dept_data_json
check ( department_data is json )
Table altered.
insert into departments_json
values ( 100, utl_raw.cast_to_raw ( 'Random junk' ) )
ORA-02290: check constraint (SQL_HLHMAXBYXUBXJAWJKCZITOSAQ.DEPT_DATA_JSON) violated ORA-06512: at "SYS.DBMS_SQL", line 1721More Details: https://docs.oracle.com/error-help/db/ora-02290
Inserting JSON in a BLOB
insert into departments_json
values ( 110, utl_raw.cast_to_raw ( '{
"department": "Accounting",
"employees": [
{
"name": "Higgins, Shelley",
"job": "Accounting Manager",
"hireDate": "2002-06-07T00:00:00"
},
{
"name": "Gietz, William",
"job": "Public Accountant",
"hireDate": "2002-06-07T00:00:00"
}
]
}' ))
1 row(s) inserted.
rollback
Statement processed.
insert into departments_json (
department_id, department_data
)
select d.department_id,
json_object (
'department' value d.department_name,
'employees' value json_arrayagg (
json_object (
'name' value last_name || ', ' || first_name,
'job' value job_title,
'hireDate' value hire_date
))
returning blob
)
from hr.departments d, hr.employees e, hr.jobs j
where d.department_id = e.department_id
and e.job_id = j.job_id
group by d.department_id, d.department_name
11 row(s) inserted.
commit
Statement processed.
Simple Dot-Notation Access
select d.department_data.department
from departments_json d
DEPARTMENT | Administration | Marketing | Purchasing | Human Resources | Shipping | IT | Public Relations | Sales | Executive | Finance | Accounting |
---|
select department_data.department
from departments_json d
ORA-00904: "DEPARTMENT_DATA"."DEPARTMENT": invalid identifierMore Details: https://docs.oracle.com/error-help/db/ora-00904
Simple Dot-Notation Access in Where
select *
from departments_json d
where d.department_data.department = 'Accounting'
DEPARTMENT_ID | DEPARTMENT_DATA | 110 | [unsupported data type] |
---|
Simple Dot-Notation Access with Arrays
select d.department_data.employees[0].name
from departments_json d
where department_id = 110
EMPLOYEES | Gietz, William |
---|
Simple Dot-Notation Access with Arrays
select d.department_data.employees[*].name
from departments_json d
where department_id = 110
EMPLOYEES | ["Gietz, William","Higgins, Shelley"] |
---|
alter table departments_json
drop constraint dept_data_json
Table altered.
TREAT ... AS JSON
with j_data as (
select treat (
d.department_data as json
) as department_data
from departments_json d
where department_id = 110
)
select j.department_data.department
from j_data j
where department_data is json
DEPARTMENT | Accounting |
---|
alter table departments_json
add constraint dept_data_json
check ( department_data is json )
Table altered.
JSON_value
select json_value (
department_data,
'$.employees[1].hireDate' returning date
) hire_date
from departments_json d
where department_id = 110
HIRE_DATE | 07-JUN-02 |
---|
JSON_value Error Clause
select json_value (
department_data,
'$.nonExistentAttribute'
) not_here
from departments_json d
where department_id = 110
NOT_HERE | - |
---|
JSON_value Error Clause
select json_value (
department_data,
'$.nonExistentAttribute'
error on error
) not_here
from departments_json d
ORA-40462: JSON_VALUE evaluated to no valueMore Details: https://docs.oracle.com/error-help/db/ora-40462
JSON_query
select json_query (
department_data,
'$.employees[*]'
returning varchar2 pretty
with wrapper
) employees
from departments_json d
where department_id = 110
EMPLOYEES | [ { "name" : "Gietz, William", "job" : "Public Accountant", "hireDate" : "2002-06-07T00:00:00" }, { "name" : "Higgins, Shelley", "job" : "Accounting Manager", "hireDate" : "2002-06-07T00:00:00" } ] |
---|
select json_query (
department_data format json,
'$.employees[*].name'
returning varchar2 pretty
with wrapper
) employee_names
from departments_json d
where department_id = 110
EMPLOYEE_NAMES | [ "Gietz, William", "Higgins, Shelley" ] |
---|
JSON_table
select j.*
from departments_json d, json_table (
d.department_data, '$' columns (
department path '$.department',
nested path '$.employees[*]'
columns (
name path '$.name',
job path '$.job'
) ) ) j
where d.department_id = 110
DEPARTMENT | NAME | JOB | Accounting | Gietz, William | Public Accountant | Accounting | Higgins, Shelley | Accounting Manager |
---|
JSON_table Simplified
select j.*
from departments_json d, json_table (
d.department_data, '$' columns (
department,
nested employees[*]
columns (
name,
job
) ) ) j
where d.department_id = 110
DEPARTMENT | NAME | JOB | Accounting | Gietz, William | Public Accountant | Accounting | Higgins, Shelley | Accounting Manager |
---|
JSON_table Simplified with Extended
select j.*
from departments_json d, json_table (
d.department_data, '$' columns (
department,
nested employees[*]
columns (
name,
job,
hire_date date path '$.hireDate'
) ) ) j
where d.department_id = 110
DEPARTMENT | NAME | JOB | HIRE_DATE | Accounting | Gietz, William | Public Accountant | 07-JUN-02 | Accounting | Higgins, Shelley | Accounting Manager | 07-JUN-02 |
---|
Updating JSON
update departments_json
set department_data = utl_raw.cast_to_raw (
'{
"department": "Finance and Accounting",
"employees": [
{
"name": "Higgins, Shelley",
"job": "Accounting Manager",
"hireDate": "2002-06-07T00:00:00"
},
{
"name": "Gietz, William",
"job": "Public Accountant",
"hireDate": "2002-06-07T00:00:00"
}
]
}'
)
where department_id = 110
1 row(s) updated.
select d.department_data.department
from departments_json d
where department_id = 110
DEPARTMENT | Finance and Accounting |
---|
rollback
Statement processed.
JSON_mergepatch
update departments_json
set department_data = json_mergepatch (
department_data,
'{
"department" : "Finance and Accounting"
}'
)
where department_id = 110
1 row(s) updated.
select d.department_data.department
from departments_json d
DEPARTMENT | Administration | Marketing | Purchasing | Human Resources | Shipping | IT | Public Relations | Sales | Executive | Finance | Finance and Accounting |
---|
rollback
Statement processed.
JSON_mergepatch with Arrays
update departments_json
set department_data = json_mergepatch (
department_data,
'{ "employees" :
[ {
"name" : "Gietz, William",
"job" : "Public Accountant",
"hireDate" : "2002-06-07T00:00:00"
},
{
"name" : "Higgins, Shelley",
"job" : "Accounting Manager",
"hireDate" : "2002-06-07T00:00:00"
},
{
"name" : "Chen, John",
"job" : "Accountant",
"hireDate" : "2005-09-28T00:00:00"
},
{
"name" : "Greenberg, Nancy",
"job" : "Finance Manager",
"hireDate" : "2002-08-17T00:00:00"
},
{
"name" : "Urman, Jose Manuel",
"job" : "Accountant",
"hireDate" : "2006-03-07T00:00:00"
}
]
}'
)
where department_id = 110
1 row(s) updated.
select d.department_data.employees[*]
from departments_json d
where department_id = 110
EMPLOYEES | [{"name":"Gietz, William","job":"Public Accountant","hireDate":"2002-06-07T00:00:00"},{"name":"Higgins, Shelley","job":"Accounting Manager","hireDate":"2002-06-07T00:00:00"},{"name":"Chen, John","job":"Accountant","hireDate":"2005-09-28T00:00:00"},{"name":"Greenberg, Nancy","job":"Finance Manager","hireDate":"2002-08-17T00:00:00"},{"name":"Urman, Jose Manuel","job":"Accountant","hireDate":"2006-03-07T00:00:00"}] |
---|
rollback
Statement processed.
JSON Function-based Indexes
create index dept_department_name_i on
departments_json (
json_value (
department_data, '$.department'
error on error
null on empty
)
)
Index created.
Enable Execution Plan Stats for the Queries
alter session set statistics_level = all
Statement processed.
select *
from departments_json d
where json_value (
department_data,
'$.department' error on error null on empty
) = 'Accounting'
DEPARTMENT_ID | DEPARTMENT_DATA | 110 | [unsupported data type] |
---|
JSON_value Index Plan
with plan_table as (
select * from v$sql_plan_statistics_all
where sql_id = '7q6v5ky2j7y3d'
)
select id "Id",
rpad(
lpad(' ',2*level) || operation || ' ' || options , 40 ) "Operation",
object_name "Name",
last_starts "Starts",
cardinality "E-Rows",
LAST_OUTPUT_ROWS "A-Rows",
LAST_CR_BUFFER_GETS+LAST_CU_BUFFER_GETS "Buffers"
from plan_table
connect by prior id = parent_id
start with id = 0
no data found
drop index dept_department_name_i
Index dropped.
JSON Search Index
create search index dept_json_i on
departments_json ( department_data )
for json
Index created.
exec dbms_stats.gather_table_stats ( null, 'DEPARTMENTS_JSON' )
Statement processed.
select *
from departments_json d
where json_value (
department_data,
'$.department'
) = 'Accounting'
DEPARTMENT_ID | DEPARTMENT_DATA | 110 | [unsupported data type] |
---|
with plan_table as (
select * from v$sql_plan_statistics_all
where sql_id = 'dyp69gwj7q9fa'
)
select id "Id",
rpad(
lpad(' ',2*level) || operation || ' ' || options , 40 ) "Operation",
object_name "Name",
last_starts "Starts",
cardinality "E-Rows",
LAST_OUTPUT_ROWS "A-Rows",
LAST_CR_BUFFER_GETS+LAST_CU_BUFFER_GETS "Buffers"
from plan_table
connect by prior id = parent_id
start with id = 0
Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers | 0 | SELECT STATEMENT | - | 1 | - | 1 | 341 | 1 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS_JSON | 1 | 1 | 1 | 341 | 2 | DOMAIN INDEX | DEPT_JSON_I | 1 | - | 1 | 340 |
---|
select sql_id, sql_text from v$sql
where sql_text like '%json_textcontains%'
and sql_text not like '%not this%'
no data found
select json_serialize ( department_data returning varchar2 pretty )
from departments_json d
where json_textcontains ( department_data, '$', 'Public' )
JSON_SERIALIZE(DEPARTMENT_DATARETURNINGVARCHAR2PRETTY) | { "department" : "Public Relations", "employees" : [ { "name" : "Baer, Hermann", "job" : "Public Relations Representative", "hireDate" : "2002-06-07T00:00:00" } ] } | { "department" : "Accounting", "employees" : [ { "name" : "Gietz, William", "job" : "Public Accountant", "hireDate" : "2002-06-07T00:00:00" }, { "name" : "Higgins, Shelley", "job" : "Accounting Manager", "hireDate" : "2002-06-07T00:00:00" } ] } |
---|
with plan_table as (
select * from v$sql_plan_statistics_all
where sql_id = '8txzkvt3paf5q'
)
select id "Id",
rpad(
lpad(' ',2*level) || operation || ' ' || options , 40 ) "Operation",
object_name "Name",
last_starts "Starts",
cardinality "E-Rows",
LAST_OUTPUT_ROWS "A-Rows",
LAST_CR_BUFFER_GETS+LAST_CU_BUFFER_GETS "Buffers"
from plan_table
connect by prior id = parent_id
start with id = 0
Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers | 0 | SELECT STATEMENT | - | 1 | - | 2 | 12 | 1 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS_JSON | 1 | 2 | 2 | 12 | 2 | DOMAIN INDEX | DEPT_JSON_I | 1 | - | 2 | 10 |
---|
JSON_exists
select department_id
from departments_json d
where json_exists (
department_data,
'$.employees.hireDate'
)
DEPARTMENT_ID | 10 | 20 | 30 | 40 | 50 | 60 | 70 | 80 | 90 | 100 | 110 |
---|
JSON_exists
select *
from departments_json d
where json_exists (
department_data,
'$.employees.salary'
)
no data found
JSON Data Guide
alter index dept_json_i
rebuild parameters ( 'dataguide on' )
Statement processed.
JSON Data Guide
exec dbms_json.add_virtual_columns ( 'departments_json', 'department_data' )
Statement processed.
select column_name from user_tab_columns
where table_name = 'DEPARTMENTS_JSON'
COLUMN_NAME | DEPARTMENT_ID | DEPARTMENT_DATA | DEPARTMENT_DATA$department |
---|
select "DEPARTMENT_DATA$department" department_name
from departments_json
where department_id = 110
DEPARTMENT_NAME | Accounting |
---|
Rename Data Guide Columns
begin
dbms_json.rename_column (
'departments_json', 'department_data',
'$.department', dbms_json.type_string,
'DEPARTMENT_NAME'
);
dbms_json.add_virtual_columns (
'departments_json', 'department_data'
);
end;
Statement processed.
select column_name from user_tab_columns
where table_name = 'DEPARTMENTS_JSON'
COLUMN_NAME | DEPARTMENT_ID | DEPARTMENT_DATA | DEPARTMENT_NAME |
---|
Data Guide Documents-to-Rows
begin
dbms_json.create_view (
'department_employees', 'departments_json',
'department_data',
dbms_json.get_index_dataguide (
'departments_json',
'department_data',
dbms_json.format_hierarchical
)
);
end;
Statement processed.
select * from department_employees
where department_id = 110
DEPARTMENT_ID | DEPARTMENT_DATA$job | DEPARTMENT_DATA$name | DEPARTMENT_DATA$hireDate | DEPARTMENT_NAME | 110 | Public Accountant | Gietz, William | 2002-06-07T00:00:00 | Accounting | 110 | Accounting Manager | Higgins, Shelley | 2002-06-07T00:00:00 | Accounting |
---|
JSON_dataguide
select json_dataguide ( department_data )
from departments_json
JSON_DATAGUIDE(DEPARTMENT_DATA) | [{"o:path":"$.employees","type":"array","o:length":4096},{"o:path":"$.employees.job","type":"string","o:length":32},{"o:path":"$.employees.name","type":"string","o:length":32},{"o:path":"$.employees.hireDate","type":"string","o:length":32},{"o:path":"$.department","type":"string","o:length":16}] |
---|
Generate JSON
select json_object (
'department' value d.department_name,
'employees' value json_arrayagg (
json_object (
'name' value first_name || ', ' || last_name,
'job' value job_title,
'hireDate' value hire_date
)
)
)
from hr.departments d
join hr.employees e
on d.department_id = e.department_id
join hr.jobs j
on e.job_id = j.job_id
where d.department_id = 110
group by d.department_name
JSON_OBJECT('DEPARTMENT'VALUED.DEPARTMENT_NAME,'EMPLOYEES'VALUEJSON_ARRAYAGG(JSON_OBJECT('NAME'VALUEFIRST_NAME||','||LAST_NAME,'JOB'VALUEJOB_TITLE,'HIREDATE'VALUEHIRE_DATE))) | {"department":"Accounting","employees":[{"name":"Shelley, Higgins","job":"Accounting Manager","hireDate":"2002-06-07T00:00:00"},{"name":"William, Gietz","job":"Public Accountant","hireDate":"2002-06-07T00:00:00"}]} |
---|
JSON_object Simplified
select json_query ( json_object ( * ) , '$' pretty )
from hr.departments
where department_id = 110
JSON_QUERY(JSON_OBJECT(*),'$'PRETTY) | { "DEPARTMENT_ID" : 110, "DEPARTMENT_NAME" : "Accounting", "MANAGER_ID" : 205, "LOCATION_ID" : 1700 } |
---|
Comparing JSON Documents
with doc1_rows as (
select t.*
from departments_json, json_table (
department_data
columns
department,
nested employees[*]
columns (
name , job
)
) t
where department_id = 110
), doc2_rows as (
select *
from json_table ( '{
"employees" :
[ {
"name" : "Gietz, William",
"job" : "Public Accountant",
"hireDate" : "2002-06-07T00:00:00"
},
{
"hireDate" : "2002-06-07T00:00:00",
"name" : "Higgins, Shelley",
"job" : "Accounting Manager"
}
],
"department" : "Accounting"
}' columns
department,
nested employees[*]
columns (
name , job
)
)
), all_rows as (
select d.*, -1 tab
from doc1_rows d
union all
select d.*, 1 tab
from doc2_rows d
)
select department, name, job
from all_rows
group by department, name, job
having sum ( tab ) <> 0
no data found
Simplified JSON Comparison with JSON_equal
select case
when json_equal ( department_data,
'{"employees" :
[
{
"name" : "Gietz, William",
"job" : "Public Accountant",
"hireDate" : "2002-06-07T00:00:00"
},
{
"hireDate" : "2002-06-07T00:00:00",
"name" : "Higgins, Shelley",
"job" : "Accounting Manager"
}
],
"department" : "Accounting"
}' ) then 'EQUAL' else 'DIFFERENT' end matching
from departments_json
where department_id = 110
MATCHING | EQUAL |
---|
Pretty-Printing Documents
select json_query (
department_data, '$' pretty
)
from departments_json
where department_id = 110
JSON_QUERY(DEPARTMENT_DATA,'$'PRETTY) | { "department" : "Accounting", "employees" : [ { "name" : "Gietz, William", "job" : "Public Accountant", "hireDate" : "2002-06-07T00:00:00" }, { "name" : "Higgins, Shelley", "job" : "Accounting Manager", "hireDate" : "2002-06-07T00:00:00" } ] } |
---|
JSON_serialize
select json_serialize (
department_data returning varchar2(10000) pretty
)
from departments_json
where department_id = 110
JSON_SERIALIZE(DEPARTMENT_DATARETURNINGVARCHAR2(10000)PRETTY) | { "department" : "Accounting", "employees" : [ { "name" : "Gietz, William", "job" : "Public Accountant", "hireDate" : "2002-06-07T00:00:00" }, { "name" : "Higgins, Shelley", "job" : "Accounting Manager", "hireDate" : "2002-06-07T00:00:00" } ] } |
---|