Oracle Live SQL - Script: JSON in Oracle Database

Statement 1

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  
)

Table created.

Statement 2

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 )

Table altered.

Statement 3

...any attempts to do so throw an exception.

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 1721

More Details: https://docs.oracle.com/error-help/db/ora-02290

Statement 4

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 5

rollback

Statement processed.

Statement 6

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 7

commit

Statement processed.

Statement 8

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


DEPARTMENT
Administration
Marketing
Purchasing
Human Resources
Shipping
IT
Public Relations
Sales
Executive
Finance
Accounting

11 rows selected.

DEPARTMENT
Administration
Marketing
Purchasing
Human Resources
Shipping
IT
Public Relations
Sales
Executive
Finance
Accounting

Statement 9

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

ORA-00904: "DEPARTMENT_DATA"."DEPARTMENT": invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 10

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_ID DEPARTMENT_DATA
110 [unsupported data type]

DEPARTMENT_ID	DEPARTMENT_DATA
110	[unsupported data type]

Statement 11

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


EMPLOYEES
Gietz, William

EMPLOYEES
Gietz, William

Statement 12

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"]

EMPLOYEES
["Gietz, William","Higgins, Shelley"]

Statement 13

alter table departments_json  
  drop constraint dept_data_json

Table altered.

Statement 14

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


DEPARTMENT
Accounting

DEPARTMENT
Accounting

Statement 15

alter table departments_json  
  add constraint dept_data_json  
  check ( department_data is json )

Table altered.

Statement 16

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_DATE
07-JUN-02

HIRE_DATE
07-JUN-02

Statement 17

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
 -

NOT_HERE
-

Statement 18

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

ORA-40462: JSON_VALUE evaluated to no value

More Details: https://docs.oracle.com/error-help/db/ora-40462

Statement 19

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"
  }
]

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 20

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"
]

EMPLOYEE_NAMES
[ "Gietz, William", "Higgins, Shelley" ]

Statement 21

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


DEPARTMENT NAME JOB
Accounting Gietz, William Public Accountant
Accounting Higgins, Shelley Accounting Manager

2 rows selected.

DEPARTMENT	NAME	JOB
Accounting	Gietz, William	Public Accountant
Accounting	Higgins, Shelley	Accounting Manager

Statement 22

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,   
             job  
       ) ) ) j  
where  d.department_id = 110


DEPARTMENT NAME JOB
Accounting Gietz, William Public Accountant
Accounting Higgins, Shelley Accounting Manager

2 rows selected.

DEPARTMENT	NAME	JOB
Accounting	Gietz, William	Public Accountant
Accounting	Higgins, Shelley	Accounting Manager

Statement 23

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,   
             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

2 rows selected.

DEPARTMENT	NAME	JOB	HIRE_DATE
Accounting	Gietz, William	Public Accountant	07-JUN-02
Accounting	Higgins, Shelley	Accounting Manager	07-JUN-02

Statement 24

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 25

select d.department_data.department   
from   departments_json d  
where  department_id = 110


DEPARTMENT
Finance and Accounting

DEPARTMENT
Finance and Accounting

Statement 26

rollback

Statement processed.

Statement 27

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.

Statement 28

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

11 rows selected.

DEPARTMENT
Administration
Marketing
Purchasing
Human Resources
Shipping
IT
Public Relations
Sales
Executive
Finance
Finance and Accounting

Statement 29

rollback

Statement processed.

Statement 30

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.

Statement 31

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"}]

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 32

rollback

Statement processed.

Statement 33

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  
    )   
  )

Index created.

Statement 34

Enable Execution Plan Stats for the Queries

alter session set statistics_level = all

Statement processed.

Statement 35

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_ID DEPARTMENT_DATA
110 [unsupported data type]

DEPARTMENT_ID	DEPARTMENT_DATA
110	[unsupported data type]

Statement 36

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

no data found

Statement 37

Remove the index to see how it compares to a JSON search index.

drop index dept_department_name_i

Index dropped.

Statement 38

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

Index created.

Statement 39

exec dbms_stats.gather_table_stats ( null, 'DEPARTMENTS_JSON' )

Statement processed.

Statement 40

select * 
from   departments_json d 
where  json_value (  
         department_data,  
         '$.department' 
) = 'Accounting'


DEPARTMENT_ID DEPARTMENT_DATA
110 [unsupported data type]

DEPARTMENT_ID	DEPARTMENT_DATA
110	[unsupported data type]

Statement 41

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


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

3 rows selected.

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

Statement 42

select sql_id, sql_text from v$sql 
where  sql_text like '%json_textcontains%' 
and    sql_text not like '%not this%'

no data found

Statement 43

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.

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" } ] }

Statement 44

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

3 rows selected.

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

Statement 45

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_ID
10
20
30
40
50
60
70
80
90
100
110

11 rows selected.

DEPARTMENT_ID
10
20
30
40
50
60
70
80
90
100
110

Statement 46

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'  
)

no data found

Statement 47

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 48

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 49

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_NAME
DEPARTMENT_ID
DEPARTMENT_DATA
DEPARTMENT_DATA$department

3 rows selected.

COLUMN_NAME
DEPARTMENT_ID
DEPARTMENT_DATA
DEPARTMENT_DATA$department

Statement 50

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_NAME
Accounting

DEPARTMENT_NAME
Accounting

Statement 51

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'   
  );  
end;

Statement processed.

Statement 52

The virtual column now has the "friendly" name, DEPARTMENT_NAME.

select column_name from user_tab_columns 
where  table_name = 'DEPARTMENTS_JSON'


COLUMN_NAME
DEPARTMENT_ID
DEPARTMENT_DATA
DEPARTMENT_NAME

3 rows selected.

COLUMN_NAME
DEPARTMENT_ID
DEPARTMENT_DATA
DEPARTMENT_NAME

Statement 53

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  
    )   
  );  
end;

Statement processed.

Statement 54

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_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

2 rows selected.

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

Statement 55

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}]

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 56

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"}]}

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 57

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
}

JSON_QUERY(JSON_OBJECT(*),'$'PRETTY)
{ "DEPARTMENT_ID" : 110, "DEPARTMENT_NAME" : "Accounting", "MANAGER_ID" : 205, "LOCATION_ID" : 1700 }

Statement 58

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   
        )  
      ) 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

Statement 59

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


MATCHING
EQUAL

MATCHING
EQUAL

Statement 60

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"
    }
  ]
}

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 61

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"
    }
  ]
}

JSON in Oracle Database

Additional Information