JSON_EQUAL Condition

The JSON_EQUAL condition compares two JSON values and returns TRUE if they are equal, FALSE otherwise.

Syntax

JSON_EQUAL(json_value, json_value [JSON_EQUAL_on_error_clause])

JSON_EQUAL_on_error_clause::= {ERROR|TRUE|FALSE|NULL} ON ERROR

Parameters

JSON_EQUAL has the parameters:

Parameter	Description
json_value	JSON value to evaluate. The JSON value can be of type JSON, VARCHAR2, or CLOB and must be valid JSON.
JSON_EQUAL_on_error_clause	Optional error clause to handle errors that occur. For example, an error occurs when input values are not well-formed JSON. Default is FALSE ON ERROR.
ERROR ON ERROR	If there is an error, and ERROR ON ERROR is specified, JSON_EQUAL returns the error.
TRUE ON ERROR	If there is an error, and TRUE ON ERROR is specified, JSON_EQUAL returns TRUE and does not return the error.
FALSE ON ERROR	If there is an error, and FALSE ON ERROR is specified, JSON_EQUAL returns FALSE and does not return the error. This is the default.
NULL ON ERROR	If there is an error, and NULL ON ERROR is specified, JSON_EQUAL returns NULL and does not return the error.

Description

• The JSON_EQUAL condition compares two JSON values and returns TRUE if they are equal, FALSE otherwise. The input values must be valid JSON data.

• You can use the NOT predicate (NOT JSON_EQUAL) to reverse the behavior of JSON_EQUAL.

• The comparison ignores insignificant whitespace and insignificant object member order. For example, JSON objects are equal if they have the same members and values, regardless of whitespace or the order of the members. In the following code snippet, even though the members are in different order, JSON_EQUAL returns TRUE:

  JSON_EQUAL ('{a:1, b:2}', '{b:2 , a:1 }');

• The order of elements within a JSON array does affect the comparison results. The JSON_EQUAL condition returns FALSE for arrays with elements in a different order. For example:

  JSON_EQUAL ('[1, 2, 3]', '[3, 2, 1]')

• The JSON_EQUAL condition supports an ON ERROR clause for the handling of errors. A typical error occurs when input values are not valid JSON. Valid values for the ON ERROR clause are as follows:

  • ERROR ON ERROR: If an error occurs, JSON_EQUAL returns the error.

  • TRUE ON ERROR: If an error occurs, JSON_EQUAL returns TRUE.

  • FALSE ON ERROR: If an error occurs, JSON_EQUAL returns FALSE. This is the default.

  • NULL ON ERROR: If an error occurs, JSON_EQUAL returns NULL.

• If either of the two compared inputs contains duplicate fields, the behavior of JSON_EQUAL is unspecified.

Examples

The following examples illustrate various uses of JSON_EQUAL. The examples use a CASE expression.

The examples use ttIsql and are run from the ttIsql Command> prompt.

This example illustrates that JSON_EQUAL returns TRUE when comparing an empty JSON object with an empty JSON object that has whitespace. The JSON_EQUAL condition ignores whitespace and considers the values semantically the same.

SELECT CASE WHEN JSON_EQUAL ('{}', '{   }' ERROR ON ERROR) THEN 'Same' ELSE 'Different' END;

The result of the CASE expression is Same.

< Same >
1 row found.

This example shows that JSON_EQUAL returns FALSE when comparing an empty JSON object with a JSON object whose value is NULL.

SELECT CASE WHEN JSON_EQUAL ('{}', 'null' ERROR ON ERROR) THEN 'Same' ELSE 'Different' END;

The result of the CASE expression is Different.

< Different >
1 row found.

This example compares two JSON objects that are the same. As expected, JSON_EQUAL returns TRUE.

SELECT CASE WHEN JSON_EQUAL ('{"NAME":"SCOTT"}', '{"NAME":"SCOTT"}' ERROR ON ERROR) THEN 'Same' ELSE 'Different' END;

The result of the CASE expression is Same.

< Same >
1 row found.

In this example, there is whitespace after the name SCOTT in the name:value pair of the first object. The JSON_EQUAL condition returns FALSE.

SELECT CASE WHEN JSON_EQUAL ('{"NAME":"SCOTT   "}', '{"NAME":"SCOTT"}' ERROR ON ERROR) THEN 'Same' ELSE 'Different' END;

The result of the CASE expression is Different.

< Different >
1 row found.

However, there can be whitespace after the name:value pair. In this case, JSON_EQUAL returns TRUE.

SELECT CASE WHEN JSON_EQUAL ('{"NAME":"SCOTT"    }', '{"NAME":"SCOTT"}' ERROR ON ERROR) THEN 'Same' ELSE 'Different' END;

The result of the CASE expression is Same.

< Same >
1 row found.

This example illustrates that case is significant when using JSON_EQUAL to compare objects. The JSON_EQUAL condition returns FALSE.

SELECT CASE WHEN JSON_EQUAL ('{"NAME":"SCOTT"}', '{"NAME":"scott"}' ERROR ON ERROR) THEN 'Same' ELSE 'Different' END;

The result of the CASE expression is Different.

< Different >
1 row found.

This example shows that when comparing two JSON objects, the order of members is insignificant. Thus, JSON_EQUAL returns TRUE.

SELECT CASE WHEN JSON_EQUAL ('{"ID":1000,"NAME":"SCOTT"}', '{"NAME":"SCOTT","ID":1000}' ERROR ON ERROR) THEN 'Same' ELSE 'Different' END;

The result of the CASE expression is Same.

< Same >
1 row found.

This example shows that the order of elements in an array is significant when using JSON_EQUAL. In this case, JSON_EQUAL returns FALSE.

SELECT CASE WHEN JSON_EQUAL ('{"list":[1,2,3,4,5]}', '{"list":[2,1,3,4,5]}' ERROR ON ERROR) THEN 'Same' ELSE 'Different' END;

The result of the CASE expression is Different.

< Different >
1 row found.

Examples Using JSON_EQUAL in the WHERE CLAUSE

The examples use ttIsql and are run from the ttIsql Command> prompt.

Let's create a table with one column containing JSON data and a second column containing JSON data. Let's compare the columns using JSON_EQUAL in a WHERE clause.

Note:

To allow you to copy the example, the output does not display the typical 1 row inserted for each of the INSERT statements. After the inserts, there are eleven rows in the table.

DROP TABLE json_table;
CREATE TABLE json_table (id TT_INTEGER NOT NULL PRIMARY KEY, json_1 JSON, json_2 JSON);
INSERT INTO json_table VALUES (1,'{}', '{   }');
INSERT INTO json_table VALUES (2, '{"a":null}','{"a":""}');
INSERT INTO json_table VALUES (3, '{"a":""}','{"a":""}');
INSERT INTO json_table VALUES (4, '{"a":null}','{"a":null}');
INSERT INTO json_table VALUES (5, '{"NAME":"SCOTT"}','{"NAME":"SCOTT"}');
INSERT INTO json_table VALUES (6, '{"NAME":"SCOTT   "}','{"NAME":"SCOTT"}');
INSERT INTO json_table VALUES (7, '{"NAME":"SCOTT"}','{"NAME":"scott"}');
INSERT INTO json_table VALUES (8, '{"ID":1000,"NAME":"SCOTT"}','{"ID":1000,"NAME":"SCOTT"}');
INSERT INTO json_table VALUES (9, '{"NAME":"SCOTT","ID":1000}','{"ID":1000,"NAME":"SCOTT"}');
INSERT INTO json_table VALUES (10, '{"list":[1,2,3,4,5,6]}','{"list":[1,2,3,4,5,6]}');
INSERT INTO json_table VALUES (11, '{"list":[2,1,3,4,5,6]}','{"list":[1,2,3,4,5,6]}');

Let's SELECT from the table.

SELECT * FROM json_table;

The output is as follows:

< 1, {}, {} >
< 2, {"a":null}, {"a":""} >
< 3, {"a":""}, {"a":""} >
< 4, {"a":null}, {"a":null} >
< 5, {"NAME":"SCOTT"}, {"NAME":"SCOTT"} >
< 6, {"NAME":"SCOTT   "}, {"NAME":"SCOTT"} >
< 7, {"NAME":"SCOTT"}, {"NAME":"scott"} >
< 8, {"ID":1000,"NAME":"SCOTT"}, {"ID":1000,"NAME":"SCOTT"} >
< 9, {"NAME":"SCOTT","ID":1000}, {"ID":1000,"NAME":"SCOTT"} >
< 10, {"list":[1,2,3,4,5,6]}, {"list":[1,2,3,4,5,6]} >
< 11, {"list":[2,1,3,4,5,6]}, {"list":[1,2,3,4,5,6]} >
11 rows found.

Let's use JSON_EQUAL to compare the JSON data in the json_1 column with the JSON data in the json_2 column.

SELECT * FROM json_table WHERE JSON_EQUAL (json_1,json_2);

The JSON_EQUAL condition returns TRUE for the following rows:

< 1, {}, {} >
< 3, {"a":""}, {"a":""} >
< 4, {"a":null}, {"a":null} >
< 5, {"NAME":"SCOTT"}, {"NAME":"SCOTT"} >
< 8, {"ID":1000,"NAME":"SCOTT"}, {"ID":1000,"NAME":"SCOTT"} >
< 9, {"NAME":"SCOTT","ID":1000}, {"ID":1000,"NAME":"SCOTT"} >
< 10, {"list":[1,2,3,4,5,6]}, {"list":[1,2,3,4,5,6]} >
7 rows found.

Note the following:

• If there is empty JSON, JSON_EQUAL returns TRUE. (Row 1).

• The JSON_EQUAL condition allows empty strings and nulls for comparison. (Rows 3 and 4 respectively).

• If the members are the same, JSON_EQUAL returns TRUE. (Rows 5 and 8).

• If the order of members is different, JSON_EQUAL returns TRUE. (Row 9).

• If the elements in an array are in the same order, JSON_EQUAL returns TRUE. (Row 10).

Next, let's review the behavior of using the NOT predicate with the JSON_EQUAL condition.

SELECT * FROM json_table WHERE NOT JSON_EQUAL (json_1,json_2);

The output is as follows:

< 2, {"a":null}, {"a":""} >
< 6, {"NAME":"SCOTT   "}, {"NAME":"SCOTT"} >
< 7, {"NAME":"SCOTT"}, {"NAME":"scott"} >
< 11, {"list":[2,1,3,4,5,6]}, {"list":[1,2,3,4,5,6]} >
4 rows found.

Note the following:

• The JSON_EQUAL condition considers NULL and the empty string not equal. (Row 2).

• The JSON_EQUAL condition considers whitespace within a name:value pair significant. (Row 6).

• The JSON_EQUAL condition considers case significant when comparing name:value pairs and returns a value of FALSE. (Row 7).

• The JSON_EQUAL condition considers the order within an array significant and returns a value of FALSE (Row 11).

Error Handing Example

This example has a syntax error. Due to the ERROR ON ERROR clause, JSON_EQUAL returns the error.

The examples use ttIsql and are run from the ttIsql Command> prompt.

SELECT JSON_EQUAL ('{"NAME":"SCOTT}', '{"NAME":"SCOTT"}' ERROR ON ERROR);

The output is as follows:

 2379: JSON syntax error : JZN-00079: missing quotation mark at end of string
The command failed.

Let's use the same JSON syntax, but change the ON ERROR clause to TRUE ON ERROR. Even though there is a syntax error, JSON_EQUAL returns TRUE due to the TRUE ON ERROR clause.

SELECT JSON_EQUAL ('{"NAME":"SCOTT}', '{"NAME":"SCOTT"}' TRUE ON ERROR);

The output is as follows:

< TRUE >
1 row found.

Next, let's use the same JSON syntax, but remove the ON ERROR clause. Since the default is FALSE ON ERROR, JSON_EQUAL returns FALSE.

SELECT JSON_EQUAL ('{"NAME":"SCOTT}', '{"NAME":"SCOTT"}');

The output is as follows:

< FALSE >
1 row found.

See Also

• About JSON in TimesTen in the Oracle TimesTen In-Memory Database JSON Developer's Guide.