Purpose
The JSON_EQUAL() function compares two JSON values and returns TRUE if they are equal. If the values are not equal, it returns FALSE. 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 valid JSON data. 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 typically indicates that the two input JSON values are not valid JSON data.
Examples
# Use the JSON_EQUAL() function as an SQL condition in a 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
# 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 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