Purpose
JSON_EQUAL() compares two JSON values and returns True if the two values are equal. If the two values are unequal, this function returns False. The input values must be valid JSON data. This function ignores spaces and the order of object members. For example, if two JSON objects contain the same members, the two JSON objects are considered equal regardless of the order of their members.
You can also use IS JSON or IS NOT JSON as an SQL condition in an SQL statement to check whether the evaluation result of an expression is valid JSON data. For more information, see Create JSON columns.
Syntax
json_equal(expr,
expr
[TRUE|FALSE|ERROR ON ERROR]);
Parameters
The default ERROR clause is FALSE ON ERROR. A typical error is that two input JSON values are not valid JSON data.
Examples
# Use the JSON_EQUAL() function as an SQL condition in the WHEN CASE statement.
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
# Use the same input values.
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 FALSE ON ERROR clause.
obclient> SELECT 1 FROM DUAL WHERE JSON_EQUAL('[1,]', '[1' FALSE ON ERROR);
Empty set
# Use the ERROR ON ERROR clause .
obclient> SELECT 1 FROM DUAL WHERE JSON_EQUAL('[1,]', '[1' error on error);
OBE-40441: JSON syntax error