Query / Insert JSON Oracle Database 12C Example

One of the new features introduced in Oracle 12C (12.1.0.2) is the support to query and insert JSON (Javascript Object Notation) data into database tables. In this article, let us explore how to query JSON data with suitable examples. You would need 12C database to try the examples presented in this post. This tutorial is tested on Oracle 12C in Linux 6.5. We will get started with a simple table and JSON column that can hold the data we want to query with;

Create Table with JSON Column

Follow the create table statement provided below:

SQL> CREATE TABLE BOOK
  2  (BOOKID NUMBER,
  3  BOOKDETAILS CLOB
  4  CONSTRAINT JSON_ENFORCE CHECK(BOOKDETAILS IS JSON));

Table created.

Here we create a table "book" with a column BOOKDETAILS that can hold JSON Data. In order to make sure only valid JSON record is inserted, we enforce a new constraint type "IS JSON" on the column. This constraint will ensure records that are not of type JSON are not inserted.(more on this later)

Insert JSON Data into Tables

Here is a simple SQL statement that you can use to insert JSON data to the table we created earlier;

SQL> INSERT INTO BOOK VALUES 
  2  (1,
  3  '{"BOOKID" : "1234",
  4  "AUTHOR" : "Thinktibits" }')
  5  /

1 row created.

Oracle throws a check constraint violation if the data you are inserting is not of type JSON or if there is a syntax error in your JSON input .As an example, we will use an improperly formatted JSON data and observe the output;

SQL> INSERT INTO BOOK VALUES
  2  (2,
  3  '{"BOOKID" : "111"')
  4  /
INSERT INTO BOOK VALUES
*
ERROR at line 1:
ORA-02290: check constraint (HR.JSON_ENFORCE) violated

Query JSON Data

We will now see how to query the data we inserted back. Let us run a SQL that will return the value of Book Id and Author. The query with output is shown below:

SQL> select p.bookdetails.BOOKID,p.bookdetails.AUTHOR from BOOK p
  2  /

BOOKID
------------------------------------------------------------------------------------------------------------------------------------
AUTHOR
------------------------------------------------------------------------------------------------------------------------------------
1234
Thinktibits

See how we have used simple object notation to query the data we inserted. You will not get any error if there is no match on the column you selected, rather you get a NULL value on the result.

Query JSON Array in Oracle

Here is an example to query nested JSON data, and the where clause of our SQL also refers to a column inside JSON input.

SQL> insert into book values
  2  (2,
  3  '{"BOOKID" : "2",
  4  "AUTHOR" : {"FNAME" : "TT","LNAME" :"LL","AGE": 25},
  5  "PAGES" : 100 }')
  6  /

1 row created.

SQL> select p.bookdetails.AUTHOR.FNAME,p.bookdetails.AUTHOR.AGE
  2  from BOOK p
  3  where p.bookdetails.BOOKID='2'
  4  /

AUTHOR
------------------------------------------------------------------------------------------------------------------------------------
AUTHOR
------------------------------------------------------------------------------------------------------------------------------------
TT
25

Note that in the example above, we have used JSON notation both in the select and where clauses.

Find all JSON columns

The view all_json_columns provides a list of all columns in the database where JSON input is enforced via IS JSON constraint. For the table we created above a sample output from this view is provided below: (there will be an entry in all_constraints also for the JSON constraint)

SQL> desc all_json_columns
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 OWNER                                                                    NOT NULL VARCHAR2(128)
 TABLE_NAME                                                               NOT NULL VARCHAR2(128)
 COLUMN_NAME                                                              NOT NULL VARCHAR2(128)
 FORMAT                                                                            VARCHAR2(9)
 DATA_TYPE                                                                         VARCHAR2(13)

SQL> select owner,table_name,column_name,format,data_type from all_json_columns
  2  / 

HR,BOOK,BOOKDETAILS,TEXT,CLOB

In the next post, we will discuss some more advanced methods of querying JSON Data in Oracle 12C. (Path expressions).