How the JSON_LENGTH() function works in Mariadb?

The JSON_LENGTH() function is a JSON function that returns the length of a JSON document.

Posted on

The JSON_LENGTH() function is a JSON function that returns the length of a JSON document. The function takes a JSON document and an optional path as arguments and returns the number of elements in the JSON document or the JSON value at the specified path. The function can be used to measure the size of a JSON document or a part of it.

Syntax

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

JSON_LENGTH(json_doc[, path])

Where json_doc is a valid JSON document and path is a valid JSON path expression.

Examples

Example 1: Getting the length of a JSON document at the root level

In this example, we create a JSON document that contains an array of numbers using the JSON_ARRAY() function. Then we use the JSON_LENGTH() function to get the length of the JSON document at the root level.

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

The output is:

+--------+
| result |
+--------+
|      3 |
+--------+

This means that the function returns the number of elements in the JSON document at the root level ($), which is 3.

Example 2: Getting the length of a JSON document at a specified path

In this example, we create a JSON document that contains an object with some key-value pairs using the JSON_OBJECT() function. Then we use the JSON_LENGTH() function to get the length of the JSON document at the path $.hobbies.

SELECT JSON_LENGTH(JSON_OBJECT('name', 'Bob', 'age', 30, 'hobbies', JSON_ARRAY('sports', 'music', 'movies')), '$.hobbies') AS result;

The output is:

+--------+
| result |
+--------+
|      3 |
+--------+

This means that the function returns the number of elements in the JSON value at the path $.hobbies, which is 3.

Example 3: Getting the length of a JSON document at a nested path

In this example, we create a JSON document that contains a nested object using the JSON_OBJECT() and JSON_ARRAY() functions. Then we use the JSON_LENGTH() function to get the length of the JSON document at the nested path $.hobbies[0].

SELECT JSON_LENGTH(JSON_OBJECT('name', 'Carol', 'age', 35, 'hobbies', JSON_ARRAY(JSON_OBJECT('sport', 'tennis', 'level', 'intermediate'), 'movies', 'coding')), '$.hobbies[0]') AS result;

The output is:

+--------+
| result |
+--------+
|      2 |
+--------+

This means that the function returns the number of elements in the JSON value at the nested path $.hobbies[0], which is 2.

Example 4: Getting NULL if the JSON document or the path are invalid or not an object or an array

In this example, we use the JSON_LENGTH() function to get the length of a JSON document at a given path. However, we use an invalid JSON document, an invalid path, or a path that is not an object or an array as arguments. The function returns NULL in these cases.

SELECT JSON_LENGTH('"name": "Dave"', '$.name') AS result;

The output is:

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

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

SELECT JSON_LENGTH(JSON_OBJECT('name', 'Eve', 'age', 40), '$**') AS result;

The output is:

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

This means that the function returns NULL because the path $** is not valid (it contains a wildcard token that is not allowed in this situation).

SELECT JSON_LENGTH(JSON_OBJECT('name', 'Frank', 'age', 45), '$.name') AS result;

The output is:

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

This means that the function returns NULL because the value at the path $.name is not an object or an array (it is a string).

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

  • JSON_KEYS(): This function returns the keys of a JSON object or the indexes of a JSON array. The function takes a JSON document and an optional path as arguments and returns a JSON array that contains the keys or indexes. If the JSON document or the path are invalid, or if the value at the path is not an object or an array, the function returns NULL.

  • 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_VALUE(): This function returns a scalar value from a JSON document at a given path. The function takes a JSON document and a path as arguments and returns a string, a number, a boolean, or a null that matches the path. If the JSON document or the path are invalid, or if the value at the path is not a scalar, the function returns NULL.

Conclusion

The JSON_LENGTH() function is a useful JSON function that can return the length of a JSON document. The function takes a JSON document and an optional path as arguments and returns the number of elements in the JSON document or the JSON value at the specified path. The function can be used to measure the size of a JSON document or a part of it. There are also some other related functions that can return the keys, type, or value of JSON documents, such as JSON_KEYS(), JSON_TYPE(), and JSON_VALUE().