JSON_EQUAL Condition

The JSON_EQUAL SQL/JSON condition compares two JSON values. It returns TRUE if the values are equal and FALSE otherwise. Unlike SQL/JSON functions and conditions that use a SQL/JSON path expression, JSON_EQUAL compares the complete input values.

JSON_EQUAL has two required arguments, and it accepts an optional ON ERROR clause.

  • Each argument is a SQL expression that returns JSON data of SQL data type JSON, VARCHAR2, or CLOB. Both input values must be valid JSON data.

  • The optional ON ERROR clause determines the behavior when an error occurs, such as when an input value is not well-formed JSON. You can specify ERROR ON ERROR, TRUE ON ERROR, FALSE ON ERROR, or NULL ON ERROR. The default behavior is FALSE ON ERROR.

The comparison uses JSON semantics rather than a character-by-character comparison:

  • Insignificant whitespace is ignored. Whitespace within a JSON string is significant.

  • The order of object members is insignificant. Objects are equal if they have the same members and values, regardless of member order.

  • The order of array elements is significant. Arrays that contain the same elements in a different order are not equal.

  • JSON member names and string values are case-sensitive.

If either input contains duplicate object fields, the result of JSON_EQUAL is unspecified.

You can use JSON_EQUAL in a CASE expression or in the WHERE clause of a SELECT statement. Use the NOT predicate (NOT JSON_EQUAL) to test whether two JSON values are not equal.

See also:

JSON_EQUAL Condition in Oracle TimesTen In-Memory Database SQL Reference.

Example 3-12 Comparing JSON Objects with JSON_EQUAL

This example compares two JSON objects whose members are in different orders. JSON_EQUAL ignores object member order and insignificant whitespace, so the condition returns TRUE and the CASE expression returns Same. 

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

The output is:

< Same >
1 row found.

Example 3-13 Comparing Arrays with JSON_EQUAL

This example compares two arrays that contain the same elements in different orders. Array element order is significant, so JSON_EQUAL returns FALSE and the CASE expression returns Different. 

SELECT CASE
         WHEN JSON_EQUAL('[1,2,3]', '[3,2,1]' 
                         ERROR ON ERROR)
         THEN 'Same'
         ELSE 'Different'
       END;

The output is:

< Different >
1 row found.

Example 3-14 Handling Errors in JSON_EQUAL

This example compares malformed JSON data with valid JSON data. Because no ON ERROR clause is specified, the default FALSE ON ERROR behavior applies. The condition returns FALSE, and the CASE expression returns Different. 

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

The output is:

< Different >
1 row found.

Specify ERROR ON ERROR to return an error for malformed JSON data. 

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

The output is:

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