Storing JSON Data in Oracle: Why and How

JSON is a simple data interchange format, an alternative to XML that’s gaining wider favor by the day especially for big data storage and REST web services. With release 12c, Oracle has introduced JSON support too – handy new features for storage and retrieval of JSON data.

Here’s a look at how JSON can be persisted in Oracle, and uses cases where doing so would be apt.

If you are new to JSON, a primer first.

Understanding the JSON format

JSON (short for JavaScript Object Notation) is a text-based format for storing data as a collection of name-value pairs. This, for example:

{
"dbname" : "Oracle",
"version" : "12c",
"rating" : "THE BEST"
}

The JSON object is enclosed in curly braces {}; the data within consists of name-value pairs separated by commas. The : (colon) symbol acts as delimiter within the name-value pairs.

Attribute names are enclosed in " (double quotes). Attribute values are enclosed in double quotes if they are of string type; numbers and boolean true/false/null are unquoted.

The JSON example above represents a Database object with three attributes: dbname, version, rating. The value of each attribute is a string type; the value appears to the right of the attribute name, after the colon.

Why would you store JSON in a relational database?

Those of us with an RDMS way of thinking might not immediately see why JSON format needs to be persisted in the database at all. Why not store relationally, get all the benefits of structure and security that comes with relational storage, and serialize/deserialize into JSON only when needed?

The question is reasonable when one wants to often do RDBMS-y things on the JSON data — for example, filtering inside the JSON, validation or restructuring the data differently for different SQLs. In such cases, relational storage is the way to go.

But let’s say we have metadata associated with the main entity — this metadata will seldom be queried on and is strongly coupled with the main entity. For example, contact details linked to a customer — storing this information in JSON might be simpler than storing it in multiple child tables with foreign keys.

Another use case is agile development — JSON-style storage is conducive to frequent/incremental schema changes.

Oracle 12c JSON Support

With native support for JSON in 12c, Oracle lets you combine the benefits of RDBMS with JSON.

SQLs can join JSON data with relational data
JSON data can be projected relationally
A table that stores JSON data can also contain columns of various other datatypes (NUMBER, VARCHAR2, etc)
A single table can have multiple columns that store JSON data
JSON data may be indexed for performance

JSON table columns: supported datatypes, IS JSON constraint

JSON data can be stored in an Oracle table using the basic SQL data types: VARCHAR2, CLOB, and BLOB (unlike XML data, which is stored using the abstract SQL data type XMLType).

Oracle recommends applying the IS JSON check constraint on columns that hold JSON data to ensure that the values are valid JSON instances.

Storing JSON in Oracle: Scripts + Demo

In this demo, we’ll create a basic table for CUSTOMER data: id, name and a JSON column called “metadata”. The JSON column will be of type VARCHAR2 with an IS JSON check constraint on it.

We’ll then load a few rows with valid JSON data, and try loading some invalid JSON data.

Script to create table with IS JSON check constraint:

-- Create customer table with JSON metadata
CREATE TABLE customer
  (custid    VARCHAR2(5) NOT NULL PRIMARY KEY,
   custname  VARCHAR2(50) NOT NULL,
   metadata  VARCHAR2(1000)
   CONSTRAINT chk_json CHECK (metadata IS JSON));

Script to load data with valid JSON into the table:

-- Insert values into table with JSON column
INSERT INTO customer
VALUES (
  1
, 'Susan'
, '{"Mobile"       : "(555) 555-1234",
    "OfficePh"     : "(651)-555-1999",
    "HomePh"       : "(651) 399-9262",
   }'
);

INSERT INTO customer
VALUES (
  2
, 'Martin'
, '{"Mobile"       : "(555) 321-4311",
    "HomePh"       : "(627)-444-2888",
    "HomePhAlt"    : "(627) 587-6623"
   }'
);

commit;

When run:

SQL> -- Create customer table with JSON metadata
SQL> CREATE TABLE customer
  2    (custid    VARCHAR2(5) NOT NULL PRIMARY KEY,
  3     custname  VARCHAR2(50) NOT NULL,
  4     metadata  VARCHAR2(1000)
  5     CONSTRAINT chk_json CHECK (metadata IS JSON));

Table created.

SQL> -- Insert values into table with JSON column
SQL> INSERT INTO customer
  2  VALUES (
  3    1
  4  , 'Susan'
  5  , '{"Mobile"       : "(555) 555-1234",
  6      "OfficePh"     : "(651)-555-1999",
  7      "HomePh"       : "(651) 399-9262",
  8     }'
  9  );

1 row created.

SQL>
SQL> INSERT INTO customer
  2  VALUES (
  3    2
  4  , 'Martin'
  5  , '{"Mobile"       : "(555) 321-4311",
  6      "HomePh"       : "(627)-444-2888",
  7      "HomePhAlt"    : "(627) 587-6623"
  8     }'
  9  );

1 row created.

SQL> commit;

Commit complete.

So far, the rows inserted in the CUSTOMER table had valid JSON data in the "metadata" column. Let’s see what happens if we try to insert some regular non-JSON text into the column.

SQL> -- Insert invalid JSON data
SQL> INSERT INTO customer
  2  VALUES (
  3    2
  4  , 'Martin'
  5  , 'This is not valid JSON'
  6  );
INSERT INTO customer
*
ERROR at line 1:
ORA-02290: check constraint (HR.CHK_JSON) violated

That generated an error. The IS JSON check constraint has made sure that any data loaded into the JSON column is a valid JSON instance.

Summary

This article gives an introduction to JSON, describes use cases for persisting JSON data in a relational database, and demonstrates how Oracle 12c supports the storage of JSON data.

The next few articles in the JSON series will show you: