8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- JSON_EQUAL Condition in 18c
- JSON Support Enhancements in Oracle Database 18c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23ai, All 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:
- JSON Developer's Guide
- JSON_EQUAL Condition in 18c
- JSON Support Enhancements in Oracle Database 18c
- JSON Quick Links : Introduction, 12cR1, 12cR2, 18c, 19c, 21c, 23ai, All Articles
Hope this helps. Regards Tim...