Search

Oracle JSON

Updated: Sep 6

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 Supposewe have a data of customer where in Address and contact details object 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

119 views
  • Facebook
  • Twitter
  • LinkedIn