How the JSON_EQUALS() function works in Mariadb?

The JSON_EQUALS() function is a JSON function that compares two JSON documents for equality.

Posted on

The JSON_EQUALS() function is a JSON function that compares two JSON documents for equality. The function takes two JSON documents as arguments and returns 1 or 0 depending on whether they are equal or not. The function compares the JSON documents in a logical way, ignoring the order of the elements in arrays and the key-value pairs in objects. The function also ignores the formatting and whitespace of the JSON documents.

Syntax

The syntax of the JSON_EQUALS() function is as follows:

JSON_EQUALS(json_doc1, json_doc2)

Where json_doc1 and json_doc2 are valid JSON documents.

Examples

Example 1: Comparing two JSON documents that are equal

In this example, we use the JSON_EQUALS() function to compare two JSON documents that are equal. We create the JSON documents using the JSON_OBJECT() and JSON_ARRAY() functions.

SELECT JSON_EQUALS(JSON_OBJECT('name', 'Alice', 'age', 25), JSON_OBJECT('age', 25, 'name', 'Alice')) AS result;

The output is:

+--------+
| result |
+--------+
|      1 |
+--------+

This means that the function returns 1 because the two JSON documents are equal. The function ignores the order of the key-value pairs in the objects.

SELECT JSON_EQUALS(JSON_ARRAY(1, 2, 3), JSON_ARRAY(3, 2, 1)) AS result;

The output is:

+--------+
| result |
+--------+
|      0 |
+--------+

This means that the function returns 1 because the two JSON documents are not equal. The function does not ignore the order of the elements in the arrays.

Example 2: Comparing two JSON documents that are not equal

In this example, we use the JSON_EQUALS() function to compare two JSON documents that are not equal. We create the JSON documents using the JSON_OBJECT() and JSON_ARRAY() functions.

SELECT JSON_EQUALS(JSON_OBJECT('name', 'Bob', 'age', 30), JSON_OBJECT('name', 'Alice', 'age', 25)) AS result;

The output is:

+--------+
| result |
+--------+
|      0 |
+--------+

This means that the function returns 0 because the two JSON documents are not equal. The function compares the values of the key-value pairs in the objects.

SELECT JSON_EQUALS(JSON_ARRAY(1, 2, 3), JSON_ARRAY(1, 2, 4)) AS result;

The output is:

+--------+
| result |
+--------+
|      0 |
+--------+

This means that the function returns 0 because the two JSON documents are not equal. The function compares the values of the elements in the arrays.

Example 3: Comparing two JSON documents that contain nested values

In this example, we use the JSON_EQUALS() function to compare two JSON documents that contain nested values. We create the JSON documents using the JSON_OBJECT() and JSON_ARRAY() functions.

SELECT JSON_EQUALS(JSON_OBJECT('name', 'Carol', 'age', 35, 'hobbies', JSON_ARRAY('music', 'movies', 'coding')), JSON_OBJECT('hobbies', JSON_ARRAY('music', 'movies', 'coding'), 'age', 35, 'name', 'Carol')) AS result;

The output is:

+--------+
| result |
+--------+
|      1 |
+--------+

This means that the function returns 1 because the two JSON documents are equal.

Example 4: Getting NULL if any of the JSON documents are invalid

In this example, we use the JSON_EQUALS() function to compare two JSON documents. However, we use an invalid JSON document as one of the arguments. The function returns NULL in this case.

SELECT JSON_EQUALS('{"name": "Dave"', JSON_OBJECT('name', 'Dave', 'age', 40)) AS result;

The output is:

+--------+
| result |
+--------+
|   NULL |
+--------+

This means that the function returns NULL because the first JSON document is not valid (it is missing a closing brace).

Example 5: Getting 0 if the JSON documents have different types

In this example, we use the JSON_EQUALS() function to compare two JSON documents that have different types. We create the JSON documents using the JSON_OBJECT() and JSON_ARRAY() functions.

SELECT JSON_EQUALS(JSON_OBJECT('name', 'Eve', 'age', 40), JSON_ARRAY('Eve', 40)) AS result;

The output is:

+--------+
| result |
+--------+
|      0 |
+--------+

This means that the function returns 0 because the two JSON documents have different types (one is an object and the other is an array).

There are some other JSON functions that are related to the JSON_EQUALS() function. Here are some of them:

  • JSON_TYPE(): This function returns the type of a JSON value. The function takes a JSON value as an argument and returns a string that indicates the type of the JSON value, such as ARRAY, OBJECT, STRING, NUMBER, BOOLEAN, or NULL.
  • JSON_EXTRACT(): This function extracts a value or values from a JSON document at a given path or paths. The function takes a JSON document and one or more path arguments. The function returns a JSON value or values that match the path or paths. If the JSON document or any of the paths are invalid, the function returns NULL.
  • JSON_SEARCH(): This function searches for a value in a JSON document and returns the path or paths to the value. The function takes a JSON document, a oneOrAll argument, a search string, and an optional path as arguments. The function returns a JSON value or values that contain the path or paths to the value. If the JSON document, the search string, or any of the paths are invalid, the function returns NULL.

Conclusion

The JSON_EQUALS() function is a useful JSON function that can compare two JSON documents for equality. The function takes two JSON documents as arguments and returns 1 or 0 depending on whether they are equal or not. The function compares the JSON documents in a logical way, ignoring the order of the elements in arrays and the key-value pairs in objects. The function also ignores the formatting and whitespace of the JSON documents. There are also some other related functions that can get the type, extract, or search for values in JSON documents, such as JSON_TYPE(), JSON_EXTRACT(), and JSON_SEARCH().