Oracle JSON

Being a relational DB developer for years it took a while for me to adapt to JASON- (JavaScript Object Notation) when Oracle first introduced it with 12c. I always contested for relational storage of data but what when we have metadata associated with multiple entity, relational storage won't work there as it would require multiple foreign key validations. It's then JASON supersede.

Let's Suppose we have a data of customer where in Address and contact details has multiple entity.

  "Address" : {
  "Street" : "Greenwood",
  "City" : "California",
  "Country" : "US",
  "Postcode" : "80111"
  },
  "ContactDetails" :
  {
  "Email" : "Steven.king@example.com",
  "Phone" : "44 123 123456",
  "Twitter" : "@steveking"
  }

Storing it through relational dB method would require tedious effort of multiple entity creation.

JASON is a text based format for storing data as a collection. JSON is a simple data interchange format, an alternative to XML that’s gaining wider favor these days especially for big data storage and REST web services.

Below is a brief to JASON and also its new features introduced in oracle 18c. I have provided few use cases to make it simple. If you are new to this concept, this blog would be suffice.

JSON object are enclosed in 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. There is no special datatype for Jason we use varchar2 ,blob, clob .

LET'S GET STARTED FIRST BY CREATING A TABLE TO HOLD JASON DATA.

CREATE TABLE json_emp (
  Emp_id RAW(16) NOT NULL,
  data CLOB,
  CONSTRAINT json_emp_pk PRIMARY KEY (emp_id),
  CONSTRAINT json_emp_json_chk CHECK (data IS JSON)
 );

INSERT INTO json_emp (emp_id, data)
 VALUES (SYS_GUID(),
  '{
  "FirstName" : "Steven",
  "LastName" : "King",
  "Job" : "Manager",
  "Address" : {
  "Street" : "Greenwood",
  "City" : "California",
  "Country" : "US",
  "Postcode" : "80111"
  },
 
  "ContactDetails" : {
  "Email" : "Steven.king@example.com",
  "Phone" : "44 123 123456",
  "Twitter" : "@steveking"
  },
  "DateOfBirth" : "01-JAN-1988",
  "Active" : true
 
  }');
 
 INSERT INTO json_emp (emp_id, data)
 VALUES (SYS_GUID(),
  '{
  "FirstName": "Neena",
  "LastName" : "Kochhar",
  "Job" : "AD_VP",
  "Address" : {
  "Street" : "100 My Street",
  "City" : "My City",
  "Country" : "UK",
  "Postcode" : "A12 34B"
  },
  "ContactDetails" : {
  "Email" : "Neene.kochaar@example.com",
  "Phone" : ""
  },
  "DateOfBirth" : "01-JAN-1982",
  "Active" : false
  }');
 
 COMMIT;

The json check constraint invalidates any invalid Jason and uses the LAX JSON syntax by default for strict json syntax we need to enforce the strict qualifier. But we need not bother much on Jason syntax in our example.

QUERY JASON DATA

DOT NOTATION:

THE individual elements of a JSON can be referenced directly from SQL using dot notation.

SELECT a.data.FirstName,
  a.data.LastName,
  a.data.Address.Postcode AS Postcode,
  a.data.ContactDetails.Email AS Email
 FROM json_emp a
 ORDER BY a.data.FirstName,
  a.data.LastName;

O/P

1 Neena Kochhar A12 34B Neene.kochaar@example.com
 2 Steven King 80111 Steven.king@example.com
 

If a non-scalar value is a referenced, the result is returned as a JSON fragment.

SELECT a.data.ContactDetails
 FROM json_emp a;
 ------------------------------------------------------------------
 1 {"Email":"Steven.king@example.com","Phone":"44 123 123456","Twitter":"@steveking"}
 
 2 {"Email":"Neene.kochaar@example.com","Phone":""}
 

IS JASON

We have already USED the IS JSON condition as part of a check constraint While creating a table to hold JSON data. The IS JSON condition can be used to test if a column contains JSON data. OR Not.

SELECT JSON_VALUE(a.data, '$.FirstName') AS first_name
 FROM json_emp a
 WHERE a.data IS JSON;
 ------------------------------------------------------------------
 1 Steven
 2 Neena
 
 

JSON_EXISTS

This allows you to make the distinction between empty and missing elements. Inthe table JASON_EMP , the second employee Neena Kochhar had a empty Twitter Account while missing phone number to make the this distinction we use JASON EXISTS

NULL VALUE

SELECT a.data.FirstName,
  a.data.LastName,
  a.data.ContactDetails.Email AS Email
 FROM json_emp a
 WHERE JSON_EXISTS(a.data.ContactDetails, '$.Phone' FALSE ON ERROR)
 AND a.data.ContactDetails.Phone IS NULL;
 
 --------------------------------------------------------
 1 Neena Kochhar Neene.kochaar@example.com
 

EMPTY VALUE

SELECT a.data.FirstName,
  a.data.LastName,
  a.data.ContactDetails.Email AS Email
 FROM json_emp a
 WHERE NOT JSON_EXISTS(a.data.ContactDetails, '$.Twitter' FALSE ON ERROR);
 
 -----------------------------------------------------------------------
 
 1 Neena Kochhar Neene.kochaar@example.com
 

JASON_VALUE

we already used JASON_VALUES in IS_JASON example. It returns the element of the JASON based on PATH.

SELECT JSON_VALUE(a.data.ContactDetails, '$.Phone') AS CONTACT_DETAILS
 FROM json_emp a
 ORDER BY 1;
 
 -----------------------------------------------------------------------
 1 44 123 123456
 2
 

NOTE: JASON SUPPORTS BOOLEAN VALUES TRUE FALSE WHICH OTHERWISE IS NOT SUPPORTED BY SQL

 SELECT
  JSON_VALUE(a.data, '$.FirstName' RETURNING VARCHAR2) AS first_name,
  JSON_VALUE(a.data, '$.LastName' RETURNING VARCHAR2(50)) AS last_name,
  JSON_VALUE(a.data, '$.Active') AS Active,
  JSON_VALUE(a.data, '$.Active' RETURNING NUMBER) AS ActiveNum
 FROM json_emp a
 ORDER BY a.data.FirstName,
  a.data.LastName;
 
 -----------------------------------------------------------------------
 
 1 Neena Kochhar false 0
 2 Steven King true 1
 
 
 
 
 

THE JASON_VALUE FUNCTION SUPPORTS VARCHAR2 , NUMBER, DATE, TIMESTAMP, BLOB, CLOB RETURN TYPE.

If returning clause is not specified or returning clause is just varchar2 as in our JSON_VALUE on first name the default is Varchar2(4000).

JSON_QUERY

To return the JASON_FRAGMENT we generally use JASON_QUERY. A with Wrapper Option is given to enclose the fragment in square bracket.

SELECT a.data.FirstName,
  a.data.LastName,
  JSON_QUERY(a.data, '$.ContactDetails' RETURNING VARCHAR2(500) WITH WRAPPER) AS contact_details
 FROM json_emp a
 ORDER BY a.data.FirstName,
  a.data.Last_name;
 
 -----------------------------------------------------------------------
 
 1 Neena Kochhar [{"Email":"Neene.kochaar@example.com","Phone":""}]
 -----------------------------------------------------------------------
 2 Steven King [{"Email":"Steven.king@example.com","Phone":"44 123 123456","Twitter":"@steveking"}]
 
 

JASON_TABLE

IT is used to represent the JASON_DATA like a relational data and includes all the functionality of - JSON_VALUE,JSON_EXISTS and JSON_QUERY.

A lot has been changed in 18c version of Oracle in JASON_TABLE to keep it simple.

SELECT jt.*
 FROM json_emp,
  JSON_TABLE(data, '$'
  COLUMNS (FirstName, LastName, Job, Active,
  NESTED ContactDetails COLUMNS (Email, Phone,Twitter)
  )
  ) jt;
 
 
  --------------------------------------------------------------------
 1 Steven King Manager true Steven.king@example.com 44 123 123456 @steveking
 -----------------------------------------------------------------------
 2 Neena Kochhar AD_VP false Neene.kochaar@example.com
 

Oracle has introduced a Materialized View (MV) refresh mechanism called ON STATEMENT refresh. With the ON STATEMENT refresh method, an MV is automatically refreshed whenever DML happens on a base table of the MV. Therefore, whenever a DML happens on any table on which a materialized view is defined, the change is automatically reflected in the materialized view. The advantage of using this approach is that the user no long needs to create a materialized view log on each of the base table in order to do fast refresh. The refresh can then avoid the overhead introduced by MV logging but still keep the materialized view refreshed all the time. This feature has been made available in JASON_TABLE.

CREATE MATERIALIZED VIEW json_emp_mv
 REFRESH FAST
 ON STATEMENT
 AS
 SELECT emp_id, jt.*
 FROM json_emp,
  JSON_TABLE(data, '$'
  COLUMNS (FirstName, LastName, Job, Active,
  NESTED ContactDetails COLUMNS (Email, Phone,twitter)
  )
  ) jt;
 
 

JSON_EQUAL

TheJSON_EQUALcondition allows you to compare two JSON documents without worrying about member order or formatting. Look at the output from the following two queries.

CREATE TABLE json_test (
  id NUMBER NOT NULL,
  data1 VARCHAR2(50),
  data2 VARCHAR2(50),
  CONSTRAINT json_equal_tab_pk PRIMARY KEY (id),
  CONSTRAINT json_equal_tab_json1_chk CHECK (data1 IS JSON),
  CONSTRAINT json_equal_tab_json2_chk CHECK (data2 IS JSON)
 );
 
 
 INSERT INTO json_test VALUES (1, '{}', '{}');
 
 -- Matching members, order and format.
 INSERT INTO json_test VALUES (2, '{"neena":"kochhar","smith":"king"}', '{"neena":"kochhar","smith":"king"}');
 
 -- Matching members and order, but differing format.
 INSERT INTO json_test VALUES (3, '{"neena":"kochhar","smith":"king"}', '{ "Neena":"Kochhar", "Smith":"King" }');
 
 -- Matching members, but differing order.
 INSERT INTO json_test VALUES (4, '{"neena":"kochhar","smith":"king"}', '{"smith":"king","neena":"kochhar"}');
 
 -- Differing members.
 INSERT INTO json_test VALUES (5, '{"neena":"kochhar","smith":"king"}', '{"smith":"king","leena":"patel"}');
 
 -- Differing members.
 INSERT INTO json_test VALUES (6, '{"neena":"kochhar","smith":"king"}', '{"smith":"king"}');
 
 -- Duplicate members.
 INSERT INTO json_test VALUES (7, '{"neena":"kochhar"}', '{"neena":"kochhar","neena":"kochhar"}');

Lets now try a regular comparison on these data.

 SELECT id,
  data1,
  data2
 FROM json_test
 WHERE data1 != data2
 ORDER BY 1;
 
 
 -----------------------------------------------------------------------
 
 1 3 {"neena":"kochhar","smith":"king"} { "Neena":"Kochhar", "Smith":"King" }
 -----------------------------------------------------------------------
 2 4 {"neena":"kochhar","smith":"king"} {"smith":"king","neena":"kochhar"}
 -----------------------------------------------------------------------
 3 5 {"neena":"kochhar","smith":"king"} {"smith":"king","leena":"patel"}
 -----------------------------------------------------------------------
 4 6 {"neena":"kochhar","smith":"king"} {"smith":"king"}
 ----------------------------------------------------------------------
 5 7 {"neena":"kochhar"} {"neena":"kochhar","neena":"kochhar"}
 
 
 

THE Output for id3 and id4 both are coming as a result of the not equal operator used in above query while they both varies just in format and order respectively :

We can see data1 and data2 for id 3 is same just it different formatting while for id4 also it differs in ordering. To limit this JASON_EQUAL has been introduced in Oracle 18C.

JASON_EQUAL can be used in sql to get the desired output. There is no native support for using JASON_EQUAL in PL/SQL block as of now but below could be workaround for that using sql.

DECLARE
  l_result NUMBER;
 BEGIN
  FOR cur_rec IN (SELECT * FROM json_test ORDER BY 1) LOOP
  SELECT CASE
  WHEN JSON_EQUAL(cur_rec.data1, cur_rec.data2) THEN 1
  ELSE 0
  END
  INTO l_result
  FROM dual;
 
  IF l_result = 1 THEN
  DBMS_OUTPUT.put_line('Equal : ' || cur_rec.data1 || ' and ' || cur_rec.data2);
  ELSE
  DBMS_OUTPUT.put_line('Not Equal : ' || cur_rec.data1 || ' and ' || cur_rec.data2);
  END IF;
  END LOOP;
 END;
 
 
 
 -----------------------------------------------------------------------
 Equal : {} and {}
 -----------------------------------------------------------------------
 Equal : {"neena":"kochhar","smith":"king"} and {"neena":"kochhar","smith":"king"}
 -----------------------------------------------------------------------
 Not Equal : {"neena":"kochhar","smith":"king"} and { "Neena":"Kochhar", "Smith":"King" }
 -----------------------------------------------------------------------
 Equal : {"neena":"kochhar","smith":"king"} and {"smith":"king","neena":"kochhar"}
 --------------------------------------------------------------------
 Not Equal : {"neena":"kochhar","smith":"king"} and {"smith":"king","leena":"patel"}
 ----------------------------------------------------------------------
 Not Equal : {"neena":"kochhar","smith":"king"} and {"smith":"king"}
 -----------------------------------------------------------------------
 Equal : {"neena":"kochhar"} and {"neena":"kochhar","neena":"kochhar

Hope this blog gives you an insight of oracle Jason table and querying data from it. For more Jason stuff please also go through my other blog.

#ORACLE_JASON, #PL/SQL, #ORACLE_DATABASE