X

Celebrating the joy and power of Oracle SQL with the Oracle Developer Advocate team

  • February 21, 2019

How to Store, Query, and Create JSON Documents in Oracle Database

Chris Saxon
Developer Advocate

JavaScript Object Notation (JSON) is a lightweight data transfer format. It's the de facto standard for document exchange.

So it's likely you'll want to send and receive JSON documents from and to your database. And store them in your tables. Oracle Database has a huge amount of functionality that makes this easy.

In this post you'll learn how to:

Let's get started!

How to Store JSON in Your Database

JSON documents are just text. So to store them all you need is a column that holds this.

Before we get into the details, a bit of a rant:

The JSON homepage describes it as:

JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate. It is based on a subset of the JavaScript Programming Language, Standard ECMA-262 3rd Edition - December 1999. JSON is a text format that is completely language independent but uses conventions that are familiar to programmers of the C-family of languages, including C, C++, C#, Java, JavaScript, Perl, Python, and many others. These properties make JSON an ideal data-interchange language

Notice how data-interchange appears twice? And storage appears, well... never!

That's because JSON is primarily for transferring data between systems.

Personally I think traditional relational tables should be the default choice for data storage. Taking JSON input and it storing as-is in your database should be the last resort.

But I know that you may just want to dump JSON in your database and be done with it. So let's review how.

As JSON is text, the obvious choice for storing it is a VARCHAR2 for small documents. I.e. those under 4,000 bytes. Or 32,767 bytes if you have enabled extended data types.

But if you're storing bigger documents, we recommend going for BLOB.

Why not the text-based CLOB you ask?

This is because BLOB stores the document as-is, avoiding character set conversions. It also means the documents may use less storage.

The downside of this is you need to do data conversions when you save or retrieve JSON in BLOBs. Luckily, as we'll see later, this is easy to do.

So to create a table that will store "large" JSON documents, run:

create table departments_json (
  department_id   integer not null primary key,
  department_data blob not null
);

Regardless of which data type you use, you'll want to verify that you're storing real JSON. And not any old junk. To do this, add the IS JSON check constraint, like so:

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

Now you've got the table, it's time to start adding documents!

How to Insert JSON in the Table

You can now save a JSON document with a standard insert. If you've used a character data type, just whack it in. If you've gone with BLOB, add text-to-binary conversion:

 insert into departments_jsonjson 
  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"
    }
  ]
}' ));

If you've added the IS JSON constraint, the database will also reject anything that's not real JSON:

 insert into departments_json
  values ( 100, utl_raw.cast_to_raw ( 'Random junk' ) );

ORA-02290: check constraint (CHRIS.DEPT_DATA_JSON) violated

Great. But what if you want to edit part of a document saved in your table?

How to Update JSON with SQL

Let's say there's a big corporate re-org, merging the Finance and Accounting departments. Now imaginatively titled "Finance and Accounting".

So you need to change the name of the Finance department.

Pre-19c, to change part of a JSON document, you had to replace the whole thing. Leading to a big, clunky update like this:

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;

Oracle Database 19c introduces a new option: JSON patching with JSON_mergepatch. This only replaces the relevant section of the document. Just specify the attribute you want to update and its new value.

So instead of having to write the whole document, you can change just the department's value like this:

update departments_json
set    department_data = json_mergepatch ( 
         department_data,
         '{
           "department" : "Finance and Accounting"
         }'
       )
where  department_id = 110 ;  

Of course, as part of this re-org you'll probably need to merge in the Finance employees too. The employees attribute is an array. To patch an array, you have to replace the whole thing.

So you'll have an update like:

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 ;

So this is still clunky if you're working with large arrays. But better than having to replace the whole document!

How to Query JSON with SQL

So now you have JSON in your database. If you want to manipulate it, you can select the whole document into your app. And process it there.

But that's a lot of data to transfer if you're only interested in a couple of attributes. It's better to fetch the parts you want from the table.

Luckily Oracle Database offers many ways to get only the values you're interested in.

Simple Dot-Notation Access

Provided you added the IS JSON constraint, you can access attributes by:

  • Selecting the column
  • Adding the JSON path to the value you want

So to get the department names, list out the path to it:

select d.department_data.department
from   departments_json d;

Note you need to prefix the columns with the table name or alias. Or you'll get an "ORA-00904: "DEPARTMENT_DATA"."DEPARTMENT": invalid identifier" error.

You can also use this to get array elements. To do this, state the index of the element you want (remembering JSON arrays are zero-indexed), then its attribute.

For example, to find the name of the first employee in department 110, use:

select d.department_data.employees[0].name
from   departments_json d
where  department_id = 110;

EMPLOYEES        
Gietz, William 

Or perhaps you want the names of all the employees in a department. To do this, get the attributes at position * (asterisk). And you'll get an array back:

 select d.department_data.employees[*].name
from   departments_json d
where  department_id = 110;

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

Remember you need the IS JSON constraint to do this.

So what if you forgot to add it?

In 18c you can get around this using the TREAT function with the AS JSON clause. Wrap this around a column in a subquery or view.

And you can use dot-notation again!

 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  

Note TREAT tells the database to consider the contents to be a JSON document. Key point being consider. If the contents aren't valid JSON, you won't get an error! And dot-notation access will fail silently, returning null instead.

This highlights a key limitation of dot-notation access. You have no control over the formatting and error handling. Everything comes back as a string (VARCHAR2(4000)). And it suppresses errors. Returning null if there's an issue accessing the element.

Which is a problem if you've got messy data. If you want more control, you can use the JSON query functions JSON_value and JSON_query.

Return a Single Value with JSON_value

Say you want to return Shelly's hire date. And return the value as a real, proper date.

Using dot-notation, the value is a VARCHAR2. So you need to to_date it afterwards. But with JSON_value, all you need to do is specify the returning value as a date (or timestamp)!

select json_value (
         department_data, 
         '$.employees[1].hireDate' returning date
       ) hire_date
from   departments_json d

Note this only works if you format datetime values in your document to ISO 8601 standards. If you've used your own, custom format mask this won't work!

If you have chosen your own date format, by default the above will return NULL for the hire date. Because the database suppresses errors. Which can make tracking down problems in your JSON hard.

For example, if you search for a non-existent attribute you get nothing back:

select json_value (
         department_data, 
         '$.nonExistentAttribute'
       ) not_here
from   departments_json d
where  department_id = 110;

NOT_HERE   
<null>

This is because the error clause defaults to NULL ON ERROR.

So how do you get an exception instead?

Set the error clause to ERROR ON ERROR:

select json_value ( 
         department_data, 
         '$.nonExistentAttribute'
           error on error
       ) not_here
from   departments_json d;

ORA-40462: JSON_VALUE evaluated to no value

JSON_value is great if you're getting a single value. I.e. not an array or object.

But what if you want to return all the details for an employee? Or all the employees in a department?

To do that, you're going to need JSON_query.

Return a Document or Array with JSON_query

The process for using JSON_query is like JSON_value. State the column holding JSON and the path you want to extract.

For example, if you want to return an array of the employees for a department, you can write:

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

Note the with wrapper clause. You need to supply this if the path matches more than one value. Otherwise the call will fail silently, returning null due to the default NULL ON ERROR clause.

You can also use this to return one of the attributes of a nested object array as an array. For example, you can return just the names of each employee:

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

This function is great if you want the result as JSON. But what if you want to display the document as traditional rows-and-columns?

Say you want a report, showing the details of each employee as separate rows?

Enter JSON_table.

Convert JSON to Relational with JSON_table

The JSON_table function enables you to transform a JSON array into rows using SQL.

This makes it easier to view the data in reports. Or join it with relational tables in your database. Or take a document and save it to traditional tables!

To use JSON_table, pass the document as the first argument. Then list out the columns you want to project in the columns clause.

To convert an array to rows, you need to make it a nested path. And state the attributes you want to expose in another columns clause.

So to turn the department documents to a row per employee, you need to:

  • Have an initial columns clause. This includes any department-level attributes you want to display.
  • Within this define a nested path, returning the employee array (employees[*]).
  • In this nesting, have another columns clause. This lists the employee attributes you want to return.

Which gives a query like:

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

Neat. But kinda clunky.

You're repeating yourself a lot. The column names match the attribute names. Making mistakes likely.

So in 18c we've simplified the syntax. If the names of the columns you're projecting match the attribute names in the document, all you need to do is list the attributes!

So you can simplify the above to:

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 

You can mix-and-match simplified and extended notation. So if you want to rename attributes, or include extra formatting you can.

For example, this adds employee's hire date to the results. In the process it renames the column. And specifies it as a DATE:

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-2002  
Accounting   Higgins, Shelley   Accounting Manager   07-JUN-2002

So far we've talked about using these functions to return parts of your document in the query. But you can also use JSON_value and JSON_object in your where clause. So you can find rows which store given values.

Which brings an important question: How do you do this efficiently?

How to Search JSON Documents in Your Database

JSON documents can be huge. Reading all these in a table storing billions of rows is going to take a while.

To do this quickly, you're going to need an index. Luckily Oracle Database has a few options to speed up your JSON search SQL.

JSON Function-based Indexes

If you know which attributes you're searching on in advance, you can create function-based indexes for your queries.

For example, say you want to allow staff to search by department name and get the corresponding document back.

So if you have a query like this:

select * from departments_json
where  json_value ( department_data, '$.department' ) = :dept_name;

To make it fast, create an index with the exact-same function you use in your where clause, like so:

create index dept_department_name_i on
  departments_json ( 
    json_value ( department_data, '$.department' ) 
  );

Be aware that this can only index one value per document. So you can't index a whole array.

And an attribute could flip between a single value and an array between documents. So this could lead to unexpected results.

To prevent this problem, and ensure index attributes are always scalars, add the ERROR ON ERROR clause. And NULL ON EMPTY to avoid errors for missing attributes.

So the full form of the above index is really:

create index dept_department_name_i on
  departments_json ( 
    json_value ( 
      department_data, '$.department'    
        error on error
        null on empty
    ) 
  );

Whatever you do, ensure the clauses you use in your where clause match those you used in the index!

So function-based indexes are fine if you know what you're looking for.

But what if you want to support ad-hoc queries?

JSON Search Index for Fast Ad-Hoc SQL

Before Oracle Database 12.2 you could create an Oracle Text index over JSON. With this in place, the database could use the index for any JSON function query.

But this was kinda messy. So in 12.2 we simplified the syntax. With a JSON search index.

To use this, first add the IS JSON constraint to the column. Then create a SEARCH index with the FOR JSON clause:

create search index dept_json_i on
  departments_json ( department_data )
  for json;

This creates an Oracle Text index behind the scenes. So now, whatever attribute you're inspecting, you can find the associated rows.

Using JSON_textcontains, you can find any document which has a given value anywhere in the path you provide.

For example, to find documents which have the value "Public" anywhere in them, use:

select *
from   departments_json d
where  json_textcontains ( department_data, '$', 'Public' );

Note this has to be the exact word. The query above won't return documents that include "Publication" unless the value "Public" is also in the same document.

The database can also use the search index if you use JSON_value in your where clause.

But it indexes the whole document for every row. Which means the index will be bigger than its function-based counterpart. And less efficient.

For example, let's load up the table with JSON documents for all the departments with employees in the HR schema.

Then compare how the indexes perform when searching for the accounting department:

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

When using the targeted, function-based index, you'll get a plan like:

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |   
------------------------------------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT                    |                        |      1 |        |      1 |00:00:00.01 |       2 |   
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS_JSON       |      1 |      1 |      1 |00:00:00.01 |       2 |   
|*  2 |   INDEX RANGE SCAN                  | DEPT_DEPARTMENT_NAME_I |      1 |      1 |      1 |00:00:00.01 |       1 |   
------------------------------------------------------------------------------------------------------------------------   
                                                                                                                           
Predicate Information (identified by operation id):                                                                        
---------------------------------------------------                                                                        
                                                                                                                           
   2 - access("D"."SYS_NC00003$"='Accounting')

But the search index uses the following plan:

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |   
-------------------------------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT            |                  |      1 |        |      1 |00:00:00.01 |      28 |      4 |   
|*  1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS_JSON |      1 |      1 |      1 |00:00:00.01 |      28 |      4 |   
|*  2 |   DOMAIN INDEX              | DEPT_JSON_I      |      1 |        |      1 |00:00:00.01 |      27 |      4 |   
-------------------------------------------------------------------------------------------------------------------   
                                                                                                                      
Predicate Information (identified by operation id):                                                                   
---------------------------------------------------                                                                   
                                                                                                                      
   1 - filter(JSON_VALUE("DEPARTMENT_DATA" FORMAT JSON , '$.department' RETURNING VARCHAR2(4000) NULL ON              
              ERROR)='Accounting')                                                                                    
   2 - access("CTXSYS"."CONTAINS"("D"."DEPARTMENT_DATA",'{Accounting} INPATH (/department)')>0) 

The key thing to note here is the buffers column. This is how many consistent gets each step does. The lower the number, the better.

The search index uses 28 consistent gets. Compared to 2 for the function-based index. That's an order of magnitude worse!

So if you know which queries you'll be running, e.g. if you're coding them in the app, use a function-based index.

Reserve search indexes for cases where you need to support ad-hoc queries.

Of course, you can create a generic search index and (many!) function-based indexes on the same column. So you can have targeted indexes for critical queries. And a fall back for rarely-executed general searches.

So we can use the JSON functions to extract values from a document. And efficiently find documents with given values.

But they rely on you knowing the structure of your JSON. You need good documentation and standards to know what the attributes are.

If your processes are… not the best, your documentation may be out-of-date. Or non-existent!

So you need to inspect every single row to find the attribute names. (Something you get for free if you used proper relational tables…)

Luckily you can find which rows (if any) have a given attribute. With JSON_exists.

Search for Attributes with JSON_exists

Say you want to know which documents have an employee with a hire date. Do that with JSON_exists by listing the path you're looking for.

Like so:

select department_id
from   departments_json d
where  json_exists (
  department_data, 
  '$.employees.hireDate'
);

DEPARTMENT_ID   
          110

Or check if you've saved anyone's salary:

select department_id
from   departments_json d
where  json_exists (
  department_data, 
  '$.employees.salary'
);

no rows selected

Using this you can easily see which documents have which attributes.

Of course, this could still lead to a lot of trial-and-error to figure out what is in your documents.

Fortunately Oracle Database has a neat way to expose a document's attributes.

The JSON Data Guide.

Convert Documents to Relational with JSON Data Guide

The JSON Data Guide allows you to add columns to a table, returning values from a JSON document. Under the hood, this creates virtual columns calling JSON_value to get the values out.

To add these columns, you need:

  • To create a JSON search index with the DATAGUIDE ON option
  • Be on Oracle Database 12.2 or higher
  • Create the columns by calling DBMS_JSON.add_virtual_columns

This will then trawl through your objects, finding the attributes and adding columns to your table. Like so:

alter index dept_json_ii 
  rebuild parameters ( 'dataguide on' )

exec dbms_json.add_virtual_columns ( 'departments_json', 'department_data' );

desc departments_json;

Name                         Null?      Type           
DEPARTMENT_ID                NOT NULL   NUMBER(38)     
DEPARTMENT_DATA              NOT NULL   BLOB           
DEPARTMENT_DATA$department              VARCHAR2(16)  

As you can see, this added a column for the department. Which has a cryptic JSON$colname name. Which is case-sensitive. Meaning you have to use double quotes to access it:

select "DEPARTMENT_DATA$department" department_name
from   departments_json
where  department_id = 110;

It'd be better if you could have properly named columns.

The good news is, you can!

DBMS_JSON includes a rename_column procedure. This maps a given attribute path and data type to a new name.

After setting this, you need to regenerate the columns. To this by calling DBMS_JSON.add_virtual_columns again. This drops and recreates the 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;
/

desc departments_json
Name                         Null?      Type           
DEPARTMENT_ID                NOT NULL   NUMBER(38)     
DEPARTMENT_DATA              NOT NULL   BLOB           
DEPARTMENT_NAME                         VARCHAR2(16)  

This is great.

But what about that employees array? How come there are no columns for that?

Well it's a one-to-many problem. There's no clear way to return all the elements of an array in a single row.

Fortunately, you can also use the JSON Data Guide to create a view. This uses JSON_table under the covers to turn an array to rows-and-columns:

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;
/

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               

Simple!

So you've added your virtual columns or created views.

But what happens if someone adds a document with a new attribute? How do you keep the virtual columns in sync?

You could call DBMS_JSON.add_virtual_columns periodically. Or come up with some DDL-trigger based solution.

Or you could let the database do it for you automagically!

If you set the search index's parameters to DATAGUIDE ON CHANGE add_vc, the database will keep the columns in sync for you:

alter index dept_json_i
  rebuild parameters ( 'DATAGUIDE ON CHANGE add_vc' );

Bear in mind this will happen whenever the index updates.

This could be handy when you're prototyping in development. But I would advise against this in production; you could end up running lots of DDL against your tables!

But what if you want to know when a new document has new attributes? And let your developers know something's changed?

Well, you can write your own, custom change procedure. This could inspect the current virtual columns. Then see if they match those in the Data Guide. And send a notification to the relevant people if they don't.

How to Generate JSON with SQL

So far we've discussed storing JSON documents as-is in your database. But what if you're doing the good thing and storing your data relationally?

And you want to return the rows as JSON?

Converting query results to JSON is a non-trivial task. Many people have built libraries to help you with this.

So Oracle Database 12.2 we added new functions to do this natively:

  • json_array
  • json_arrayagg
  • json_object
  • json_objectagg

How you use these is pretty self-explanatory.

The array functions return the arguments as an array (surrounded by square brackets).

The object functions return the attribute/value pairs as an object (surrounded by curly braces).

The non-AGG versions return a document for each row in the input. Whereas the AGG varieties can combine many rows into one document or array. According to your GROUP BY.

So if you want to create the document we started with, from the standard HR schema, you can do this:

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

To understand what's going on, it's best to work from the inside out.

First, we create a JSON object for each employee.

json_object (
  'name'     value last_name || ', ' || first_name, 
  'job'      value job_title,
  'hireDate' value hire_date
)

Then combine these into an array for each department:

json_arrayagg (
  json_object (
    'name'     value last_name || ', ' || first_name, 
    'job'      value job_title,
    'hireDate' value hire_date
  )
)

Finally we add the department attributes, returning the finished document:

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

Note that on the first release these functions had limited data type support: just VARCHAR2, NUMBER, and DATE.

In 18c we've finished off the implementation. So they support almost all the data types in Oracle Database.

But like JSON_table, these can be tricky to work with. Converting a row to a JSON object is tedious if you have many columns.

So in 19c, we've made it easier!

Pass * to JSON_object and it'll generate an object from all the columns returned by your query:

select json_object ( * ) jdoc
from   hr.departments
where  department_id = 110;

JDOC                                                                        
{
  "DEPARTMENT_ID" : 110,
  "DEPARTMENT_NAME" : "Accounting",
  "MANAGER_ID" : 205,
  "LOCATION_ID" : 1700
}   

So these functions make it easy to convert your data to JSON. And it's simple to use JSON_table to receive documents and store the data relationally.

If you do this, it's inevitable that at some point you'll want to generate back out the same document you stored in the first place.

Which brings the important question:

How do you ensure the JSON you received and generated are the same?

How to Compare JSON Documents using SQL

At first glance this seems like a simple problem. JSON is just text. So you can just check if one document equals the other.

Right?

Well, according to the JSON standard:

  • Insignificant whitespace is irrelevant
  • Insignificant attribute order is irrelevant

So when comparing documents, first you need to strip out all the whitespace surrounding attributes and their values. Tricky, but doable.

But comparing documents which have their attributes in a different order?! Well that's another matter.

One way to do this, is to convert the documents to their relational form. Then you can do set difference operations in SQL to find any mismatches:

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 rows selected

Yuck.

So we've addressed this in 18c. There's a new condition, JSON_equal. This takes two documents as input. And returns true if they're the same, false if they're different. According to JSON standard rules.

So you can replace all the code above with:

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  

Nice!

Note this doesn't tell you which parts are different. Just that they are. So if you need to show which attributes are different, you'll need to go down the relational shredding approach.

One final thing before we finish. I promised it would be easy to convert JSON stored as BLOB to text.

How?

How to Pretty Print JSON

Simple.

The JSON_query function includes a PRETTY clause. Include this at the end and it'll return the document as pretty-printed text:

select json_query (
         department_data, '$' pretty
       ) 
from   departments_json;

Or, from 19c, you can use the new JSON_serialize function. This allows you to convert JSON from and to binary by specifying the return type as VARCHAR2, CLOB or BLOB.

And it includes a PRETTY clause :)

So you can get a nicely formatted document like so:

select json_serialize (
         department_data returning varchar2 pretty 
       ) 
from   departments_json; 

So that was a quick tour of JSON in Oracle Database. So far we've concentrated on working with JSON in SQL.

But if you're building PL/SQL APIs, chances are you'll want to work with JSON there too. The good news is we've created some object types to do just that.

If you want to try out the example in this post, you can find them all in this Live SQL script.

Or if you want to get into the details of how this works or any of the other options discussed above, read the JSON Developer's Guide.

So now we'd like to know:

Are you using JSON in your database? How? Any other options you'd like to see to make this easier to work with?

Let us know in the comments!

Join the discussion

Comments ( 1 )
  • Rajeshwaran, Jeyabal Monday, March 25, 2019
    Nice write up. Thanks.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha