Purpose
The JSON_EQUAL() function compares two JSON values and returns Ture if they are equal. It returns False if they are not equal. The input values must be valid JSON data. The comparison ignores spaces and the order of object members. For example, two JSON objects with the same members are considered equal regardless of the order of the members.
Additionally, IS JSON and IS NOT JSON are used as SQL conditions in SQL statements to check whether the result of an expression is in a valid JSON data format. For more information, see Create columns of the JSON data type.
Syntax
json_equal(expr,
expr
[TRUE|FALSE|ERROR ON ERROR]);
Syntax
The ERROR clause defaults to FALSE ON ERROR, which is typical when the two input JSON values are not valid JSON data.
Examples
# Use the JSON_EQUAL() function as an SQL condition in a WHEN CASE clause.
obclient> SELECT CASE
WHEN JSON_EQUAL (
'{"comparesAttributes":"and values"}', '{"inTwoJSONDocuments": "for equality"}'
) THEN 'THE SAME' ELSE 'DIFFERENT'
END JSON_COMPARE
FROM DUAL;
+--------------+
| JSON_COMPARE |
+--------------+
| DIFFERENT |
+--------------+
1 row in set
# Same input
obclient> SELECT 1 FROM DUAL WHERE JSON_EQUAL('[1,]', '[1]' FALSE ON ERROR);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set
obclient> SELECT 1 FROM DUAL WHERE JSON_EQUAL('[1,]', '[1' TRUE ON ERROR);
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set
# Use the ERROR clause with FALSE ON ERROR
obclient> SELECT 1 FROM DUAL WHERE JSON_EQUAL('[1,]', '[1' FALSE ON ERROR);
Empty set
# Use the ERROR clause with ERROR ON ERROR
obclient> SELECT 1 FROM DUAL WHERE JSON_EQUAL('[1,]', '[1' error on error);
ORA-40441: JSON syntax error