A Comparison of JSON Values in a Database

In this article, I will talk about some of the new SQL functions that come with Oracle 18c. I hope this article is useful in the sense of awareness. 

As we know, Oracle 18c was announced in February last year, and users are made available for use in Cloud environments. Following this announcement, Oracle has introduced a new version for its end users via the LiveSQL platform. 

I will perform the function tests that are the subject of this article through the LiveSQL platform.

The new SQL functions, which are the subject of the article, check the content of the JSON data we have stored in the database and understand if the JSONs match. With the existing infrastructure, it is necessary to develop complex SQL queries in order to be able to control the content of JSONs, and this control can be done very quickly and easily with newly developed functions.

Now let's look at what we can do by trying a few different scenarios. Let's start by creating a sample table and data.

CREATE TABLE comp_json_values
(
   json_val_1   VARCHAR2 (2000),
   json_val_2   VARCHAR2 (2000),
   use_case_desc VARCHAR2(2000)
);

INSERT INTO comp_json_values
     VALUES (
               '{"key":"k1","value":"v1"}',
               '{"key":"k1","value":"v1"}',
               'Exactly Same');

INSERT INTO comp_json_values
     VALUES (
               '{"key":"k1","value":"v1"}',
               '{"key":"k1" ,  "value":"v1"   }',
               'Exactly Same but having whitespaces');    

INSERT INTO comp_json_values
     VALUES (
               '{"key":"k1","value":"v1"}',
               '{"key":"k2","value":"v2"}',
               'Format:Same - Order:Same - Values:Different');

INSERT INTO comp_json_values
     VALUES (
               '{"key":"k1","value":"v1"}',
               '{"key":"k2","value":"v2","value2":"v3"}',
               'Format:Different - Order:Different - Values:Different');

INSERT INTO comp_json_values
     VALUES (
               '{"key":"k1","value":"v1"}',
               '{"value":"v1","key":"k1"}',
               'Format:Same - Order:Different - Values:Same');

INSERT INTO comp_json_values
     VALUES (
               '{"key":"k1","value":"v1"}',
               '{"value":"v21","key":"k21"}',
               'Format:Same - Order:Different - Values:Different');

INSERT INTO comp_json_values
     VALUES (
               '{"key":"k1","value":"v1"}',
               '{"value2":"v2","key2":"k2"}',
               'Format:Different - Order:Different - Values:Different');

COMMIT;

select * from comp_json_values;

Now let's check the JSON data with the functions that the new subroutine has to offer. In what scenario do we get the results?

SELECT json_val_1,
       json_val_2,
       use_case_desc,
       CASE
          WHEN json_equal (json_val_1, json_val_2) THEN 'Match'
          ELSE 'Mismatch'
       END
          json_comp
FROM comp_json_values;

We can use this in the CASE, WHEN, and WHERE clauses.

SELECT json_val_1, json_val_2, use_case_desc
  FROM comp_json_values
 WHERE json_equal (json_val_1, json_val_2);

select json_val_1, 
       json_val_2, 
       use_case_desc
from comp_json_values
where not json_equal(json_val_1,json_val_2);

The new functions that come with the new infrastructure can also be used in PL/SQL basically.

Thanks, and let me know your thoughts in the comments section.