Oracle and JSON

JSON stands for Java Script Object Notation. It is a way to describe and store data. It can be read and modified in any editor. It has components that describe the data and components that store the data. An example is subsequent line:

{"job_id":"AD_PRES","job_title":"President","min_salary":20080,"max_salary":40000}

Components like “job_id”, “job_title”, “min_salary”, “max_salary” describe the data, whereas “AD_PRES”, “President”, 20080, 40000 are actual data. Components like “{” and “}”provide a begin and an end respectively. The colon provides the relation between a description and actual data. A comma provides a separation between the elements in one object.

Oracle knows how to query such a JSON file. One may insert JSON statements in a table and subsequently query it. The DDL to set up such table is:

CREATE TABLE "HR"."JSON_DATA" 
   (    "UNIQUE_ID" NUMBER, 
        "JSON_COL" CLOB, 
        CONSTRAINT "MUST_BE_JSON" CHECK (json_col IS JSON) ENABLE
   );

This table has one constraint: whatever is stored in the json_col must comply to JSON rules. When one tries to insert something non-compliant, an error ORA-02290 is thrown.

One may insert records with standard insert statements, like:

Insert into HR.JSON_DATA (UNIQUE_ID, json_col) values ('11','{"job_id":"PU_CLERK","job_title":"Purchasing Clerk","min_salary":2500,"max_salary":5500}');

When a table is loaded with such statements, one may start querying it:

select J.json_col.job_title from json_data j;

In such query, a dot notation is followed. One recognizes the table, the column and the describing element that allows to retrieve a particular set of values.