8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 18c » Here

JSON_EQUAL Condition in Oracle Database 18c

The JSON_EQUAL condition was introduced in 18c to allow JSON documents to be compared regardless of member order or document formatting.

Related articles.

Setup

The examples in this article require the following test table. Each row contains two JSON documents that will be compared.

--DROP TABLE json_equal_tab PURGE;

CREATE TABLE json_equal_tab (
  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)
);

-- Matching empty JSON.
INSERT INTO json_equal_tab VALUES (1, '{}', '{}');

-- Matching members, order and format.
INSERT INTO json_equal_tab VALUES (2, '{"name1":"value1","name2":"value2"}', '{"name1":"value1","name2":"value2"}');

-- Matching members and order, but differing format.
INSERT INTO json_equal_tab VALUES (3, '{"name1":"value1","name2":"value2"}', '{ "name1":"value1", "name2":"value2" }');

-- Matching members, but differing order.
INSERT INTO json_equal_tab VALUES (4, '{"name1":"value1","name2":"value2"}', '{"name2":"value2","name1":"value1"}');

-- Differing members.
INSERT INTO json_equal_tab VALUES (5, '{"name1":"value1","name2":"value2"}', '{"name2":"value2","name3":"value3"}');

-- Differing members.
INSERT INTO json_equal_tab VALUES (6, '{"name1":"value1","name2":"value2"}', '{"name2":"value2"}');

-- Duplicate members.
INSERT INTO json_equal_tab VALUES (7, '{"name1":"value1"}', '{"name1":"value1","name1":"value1"}');
COMMIT;

The Problem

If you do a regular string comparisons between two JSON documents they will only be seen as matching if all the members are in the same order and there is no difference in formatting. We can see this using the following two queries.

SET LINESIZE 120
COLUMN data1 FORMAT A40
COLUMN data2 FORMAT A40

SELECT id,
       data1,
       data2
FROM   json_equal_tab
WHERE  data1 = data2
ORDER BY 1;
 
        ID DATA1                                    DATA2
---------- ---------------------------------------- ----------------------------------------
         1 {}                                       {}
         2 {"name1":"value1","name2":"value2"}      {"name1":"value1","name2":"value2"}

SQL>


SELECT id,
       data1,
       data2
FROM   json_equal_tab
WHERE  data1 != data2
ORDER BY 1;

        ID DATA1                                    DATA2
---------- ---------------------------------------- ----------------------------------------
         3 {"name1":"value1","name2":"value2"}      { "name1":"value1", "name2":"value2" }
         4 {"name1":"value1","name2":"value2"}      {"name2":"value2","name1":"value1"}
         5 {"name1":"value1","name2":"value2"}      {"name2":"value2","name3":"value3"}
         6 {"name1":"value1","name2":"value2"}      {"name2":"value2"}
         7 {"name1":"value1"}                       {"name1":"value1","name1":"value1"}

SQL>

The rows with IDs 3 and 4 in the output from the second query contain matching JSON documents, but because of member order or formatting differences they are seen as not matching.

The JSON_EQUAL condition goes some way to solving this problem.

JSON_EQUAL Condition (The Solution?)

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

SET LINESIZE 120
COLUMN data1 FORMAT A40
COLUMN data2 FORMAT A40

SELECT id,
       data1,
       data2
FROM   json_equal_tab
WHERE  JSON_EQUAL(data1, data2)
ORDER BY 1;

        ID DATA1                                    DATA2
---------- ---------------------------------------- ----------------------------------------
         1 {}                                       {}
         2 {"name1":"value1","name2":"value2"}      {"name1":"value1","name2":"value2"}
         3 {"name1":"value1","name2":"value2"}      { "name1":"value1", "name2":"value2" }
         4 {"name1":"value1","name2":"value2"}      {"name2":"value2","name1":"value1"}
         7 {"name1":"value1"}                       {"name1":"value1","name1":"value1"}

SQL>


SELECT id,
       data1,
       data2
FROM   json_equal_tab
WHERE  NOT JSON_EQUAL(data1, data2)
ORDER BY 1;

        ID DATA1                                    DATA2
---------- ---------------------------------------- ----------------------------------------
         5 {"name1":"value1","name2":"value2"}      {"name2":"value2","name3":"value3"}
         6 {"name1":"value1","name2":"value2"}      {"name2":"value2"}

SQL>

This is better than before, but notice the row with the ID of 7. The documentation says, "if either of two compared objects has one or more duplicate fields then the value returned by JSON_EQUAL is unspecified". That may be a problem if you have duplicates in your JSON documents.

PL/SQL Support

There is currently no native PL/SQL support for the JSON_EQUAL condition, as document here.

SET SERVEROUTPUT ON
BEGIN
  FOR cur_rec IN (SELECT * FROM json_equal_tab ORDER BY 1) LOOP
    IF JSON_EQUAL(cur_rec.data1, cur_rec.data2) THEN
      DBMS_OUTPUT.put_line(cur_rec.data1 || ' equal ' || cur_rec.data2);
    ELSE
      DBMS_OUTPUT.put_line(cur_rec.data1 || ' not equal ' || cur_rec.data2);
    END IF;
  END LOOP;
END;
/
    IF JSON_EQUAL(cur_rec.data1, cur_rec.data2) THEN
       *
ERROR at line 3:
ORA-06550: line 3, column 8:
PLS-00201: identifier 'JSON_EQUAL' must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored


SQL>

If you need to use it in PL/SQL you will have to use SQL.

SET SERVEROUTPUT ON
DECLARE
  l_result  NUMBER;
BEGIN
  FOR cur_rec IN (SELECT * FROM json_equal_tab 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     : {"name1":"value1","name2":"value2"} and {"name1":"value1","name2":"value2"}
Equal     : {"name1":"value1","name2":"value2"} and { "name1":"value1", "name2":"value2" }
Equal     : {"name1":"value1","name2":"value2"} and {"name2":"value2","name1":"value1"}
Not Equal : {"name1":"value1","name2":"value2"} and {"name2":"value2","name3":"value3"}
Not Equal : {"name1":"value1","name2":"value2"} and {"name2":"value2"}
Equal     : {"name1":"value1"} and {"name1":"value1","name1":"value1"}

PL/SQL procedure successfully completed.

SQL>

Documentation

At the time of writing the JSON_EQUAL condition is missing from the SQL Language Reference, although it is mentioned in several manuals, including the new JSON Developer's Guide. I assume this is an oversight.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.