The recommended data type to store large JSON documents is BLOB.
Create a Table to Store JSON
create table departments_json (
department_id integer not null primary key,
department_data blob not null
This stops you inserting any text you want in the JSON column.
Add an IS JSON constraint to the table
alter table departments_json
add constraint dept_data_json
check ( department_data is json )
...any attempts to do so throw an exception.
insert into departments_json
values ( 100, utl_raw.cast_to_raw ( 'Random junk' ) )
To insert text into a BLOB column, you need to convert it to binary. Calling utl_raw.cast_to_raw does this for you.
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.
Statement
This loads the departments and their associated employees. There is one row/department with employee details in an array.
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.
Statement
With the IS JSON constraint in place, you can access values by listing their path.
Simple Dot-Notation Access
select d.department_data.department
from departments_json d
DEPARTMENTAdministrationMarketingPurchasingHuman ResourcesShippingITPublic RelationsSalesExecutiveFinanceAccounting
11 rows selected.
Statement
Note you need to alias the column name to use dot-notation access. Or you'll get an ORA-00904 error.
select department_data.department
from departments_json d
You can also use dot-notation in your where clause to find documents with given values.
Simple Dot-Notation Access in Where
select *
from departments_json d
where d.department_data.department = 'Accounting'
DEPARTMENT_IDDEPARTMENT_DATA110[unsupported data type]
Statement
You can get values for array elements by stating their index with dot-notation.
Simple Dot-Notation Access with Arrays
select d.department_data.employees[0].name
from departments_json d
where department_id = 110
EMPLOYEESGietz, William
Statement
Using asterisk for the array index returns all the matching value as an array.
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"]
Statement
If you don't have an IS JSON constraint on your data, you can still access values with dot-notation. To do this you first need to wrap the column in TREAT ... AS JSON function.
This option was added in Oracle Database 18c.
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
DEPARTMENTAccounting
alter table departments_json
add constraint dept_data_json
check ( department_data is json )
You can also use JSON_value to access values in your document. This has more formatting and error handling options than dot-notation.
JSON_value
select json_value (
department_data,
'$.employees[1].hireDate' returning date
) hire_date
from departments_json d
where department_id = 110
HIRE_DATE07-JUN-02
Statement
By default the JSON* functions have the clause NULL ON ERROR.
JSON_value Error Clause
select json_value (
department_data,
'$.nonExistentAttribute'
) not_here
from departments_json d
where department_id = 110
NOT_HERE -
Statement
To return an error instead, set this clause to ERROR ON ERROR.
JSON_value Error Clause
select json_value (
department_data,
'$.nonExistentAttribute'
error on error
) not_here
from departments_json d
JSON_value can only return single-valued attributes. To return objects or arrays instead, use JSON_query.
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"
Statement
You can convert JSON document to relational rows-and-columns using JSON_table.
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
DEPARTMENTNAMEJOBAccountingGietz, WilliamPublic AccountantAccountingHiggins, ShelleyAccounting Manager
2 rows selected.
Statement
Oracle Database 18c simplified the syntax for JSON_table. As long as the names of the columns you're returning match the attribute names, just list the path to these.
JSON_table Simplified
select j.*
from departments_json d, json_table (
d.department_data, '$' columns (
department,
nested employees[*]
columns (
name,
) ) ) j
where d.department_id = 110
DEPARTMENTNAMEJOBAccountingGietz, WilliamPublic AccountantAccountingHiggins, ShelleyAccounting Manager
2 rows selected.
Statement
You can use both simplified and extended syntax in the same JSON_table call.
JSON_table Simplified with Extended
select j.*
from departments_json d, json_table (
d.department_data, '$' columns (
department,
nested employees[*]
columns (
name,
hire_date date path '$.hireDate'
) ) ) j
where d.department_id = 110
DEPARTMENTNAMEJOBHIRE_DATEAccountingGietz, WilliamPublic Accountant07-JUN-02AccountingHiggins, ShelleyAccounting Manager07-JUN-02
2 rows selected.
Statement
Before 19c, if you wanted to change a single value in a document, you had to replace the whole thing!
This changes just the department to "Finance and Accounting".
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.
Statement
From 19c you can update a document using JSON_mergepatch. With this, you only need to specify the attributes you're changing.
JSON_mergepatch
update departments_json
set department_data = json_mergepatch (
department_data,
"department" : "Finance and Accounting"
where department_id = 110
1 row(s) updated.
DEPARTMENTAdministrationMarketingPurchasingHuman ResourcesShippingITPublic RelationsSalesExecutiveFinanceFinance and Accounting
11 rows selected.
Statement
If you want to update part of an array, you still need to replace the whole thing!
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.
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"}]
Statement
You can create indexes using JSON functions. This allows you to efficiently search for documents with given values.
JSON Function-based Indexes
create index dept_department_name_i on
departments_json (
json_value (
department_data, '$.department'
error on error
null on empty
Enable Execution Plan Stats for the Queries
alter session set statistics_level = all
Run the query to see how the database uses in the index in the plan.
select *
from departments_json d
where json_value (
department_data,
'$.department' error on error null on empty
) = 'Accounting'
DEPARTMENT_IDDEPARTMENT_DATA110[unsupported data type]
Statement
The function-based index enabled nice efficient access of this document, using just 3 consistent gets.
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
Remove the index to see how it compares to a JSON search index.
drop index dept_department_name_i
Added in 12.2, a JSON search index support "any" query against your document. So you can use this for ad-hoc queries.
JSON Search Index
create search index dept_json_i on
departments_json ( department_data )
for json
The function-based index is much more effective than a search index. To find the Accounting department, the search index needed 30 consistent gets.
The JSON_value index needed just 3!
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
IdOperationNameStartsE-RowsA-RowsBuffers0 SELECT STATEMENT - 1 - 13411 TABLE ACCESS BY INDEX ROWID DEPARTMENTS_JSON1113412 DOMAIN INDEX DEPT_JSON_I1 - 1340
3 rows selected.
select sql_id, sql_text from v$sql
where sql_text like '%json_textcontains%'
and sql_text not like '%not this%'
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"
2 rows selected.
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
IdOperationNameStartsE-RowsA-RowsBuffers0 SELECT STATEMENT - 1 - 2121 TABLE ACCESS BY INDEX ROWID DEPARTMENTS_JSON122122 DOMAIN INDEX DEPT_JSON_I1 - 210
3 rows selected.
Statement
JSON_exists enables you to find all the documents that contain a given attribute. This searches for documents that have hireDate within an employee object.
JSON_exists
select department_id
from departments_json d
where json_exists (
department_data,
'$.employees.hireDate'
DEPARTMENT_ID102030405060708090100110
11 rows selected.
Statement
There are no documents that have a salary attribute under employees.
JSON_exists
select *
from departments_json d
where json_exists (
department_data,
'$.employees.salary'
Creating a JSON search index with the parameters DATAGUIDE ON enables the JSON Data Guide. You can rebuild existing indexes if you want to add this.
JSON Data Guide
alter index dept_json_i
rebuild parameters ( 'dataguide on' )
Statement processed.
Statement
With the data guide in place, you can expose scalar attributes as virtual columns.
JSON Data Guide
exec dbms_json.add_virtual_columns ( 'departments_json', 'department_data' )
Statement processed.
Statement
DEPARTMENT_DATA$department is a virtual column. This returns values for the attributes $.department.
select column_name from user_tab_columns
where table_name = 'DEPARTMENTS_JSON'
COLUMN_NAMEDEPARTMENT_IDDEPARTMENT_DATADEPARTMENT_DATA$department
3 rows selected.
Statement
The generated columns are case-sensitive. So you need to wrap the virtual columns in quotes to access them.
select "DEPARTMENT_DATA$department" department_name
from departments_json
where department_id = 110
DEPARTMENT_NAMEAccounting
Statement
You can give data guide columns "friendly" names by calling dbms_json.rename_column. You need to re-generate the virtual columns afterwards for this to come into effect.
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'
Statement processed.
Statement
The virtual column now has the "friendly" name, DEPARTMENT_NAME.
select column_name from user_tab_columns
where table_name = 'DEPARTMENTS_JSON'
COLUMN_NAMEDEPARTMENT_IDDEPARTMENT_DATADEPARTMENT_NAME
3 rows selected.
Statement
The data guide doesn't add columns for array attributes. To display these, you can build a view on the column.
This explodes the document into relational rows-and-columns, just like JSON_table.
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
Statement processed.
Statement
As with the virtual columns, by default the view's JSON attributes have columns named "JSONCOL$attrname".
select * from department_employees
where department_id = 110
DEPARTMENT_IDDEPARTMENT_DATA$jobDEPARTMENT_DATA$nameDEPARTMENT_DATA$hireDateDEPARTMENT_NAME110Public AccountantGietz, William2002-06-07T00:00:00Accounting110Accounting ManagerHiggins, Shelley2002-06-07T00:00:00Accounting
2 rows selected.
Statement
The JSON_dataguide function returns the "schema" for the JSON documents stored in a column. This allows you to see what the attributes are and their data types.
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}]
Statement
Added in 12.2, you can create JSON documents using JSON_object, JSON_array, JSON_objectagg, and JSON_arrayagg. This query returns:
* A document per department_name (from the GROUP BY)
* This contains an array of the employees in each department (JSON_arrayagg)
* Each employee has their details in a JSON object within the array, created using JSON_object
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"}]}
Statement
From 19c, you can pass * (asterisk) to JSON_object. This returns a document using all the table's columns.
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
Statement
You need to ignore whitespace and attribute order when comparing JSON documents. So to see if two documents are the same, you had to first convert them to traditional rows-and-columns. Then use set operations to see if all the attributes and values match.
Comparing JSON Documents
with doc1_rows as (
select t.*
from departments_json, json_table (
department_data
columns
department,
nested employees[*]
columns (
name , job
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
18c made document comparison easier by adding JSON_equal. Just pass the two documents to this. It will then return true if they're the same, false if they're not.
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
MATCHINGEQUAL
Statement
You can use the PRETTY clause of JSON_query to return a pretty-printed document.
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"
Statement
Starting in Oracle Database 19c, you can use JSON_serialize to convert documents between VARCHAR2, CLOB, and BLOB.
This also has a PRETTY clause, which returns pretty-printed documents.
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"
SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database
|
Terms of Use
