MariaDB JSON_LENGTH() Function

In MariaDB, JSON_LENGTH() is a built-in function that returns the length of a JSON document or a node within a JSON document specified by a path.

MariaDB JSON_LENGTH() Syntax

Here is the syntax for the MariaDB JSON_LENGTH() function:

JSON_LENGTH(json)
JSON_LENGTH(json, path)

Parameters

json

Required. A JSON document.

path

Optional. A path expression.

If you supply the wrong number of arguments, MariaDB will report an error: ERROR 1582 (42000): Incorrect parameter count in the call to native function 'JSON_LENGTH'.

Return value

If specified path, the MariaDB JSON_LENGTH() function returns the length of the value specified by path in the JSON document, otherwise returns the length of the JSON document. This function calculates the length of a JSON document according to the following rules:

• The length of the scalar is 1. For example, the length of 1, '"x"', true, false, or null is 1.
• The length of an array is the number of array elements. For example, the length of [1, 2] is 2.
• The length of an object is the number of object members. For example, the length of {"x": 1} is 1.
• Embedded arrays or objects do not participate in the calculation of length. For example, the length of {"x": [1, 2]} is 1.

The JSON_LENGTH() function will return NULL if:

• The specified path does not exist in the JSON document.
• Any parameter is NULL.

MariaDB JSON_LENGTH() Examples

Here are some common examples to show the usages of the Mariadb JSON_LENGTH() function.

Example: length of scalars

SELECT
    JSON_LENGTH('1') AS `1`,
    JSON_LENGTH('true') AS `true`,
    JSON_LENGTH('false') AS `false`,
    JSON_LENGTH('null') AS `null`,
    JSON_LENGTH('"abc"') AS `"abc"`;

Output:

+------+------+-------+------+-------+
| 1    | true | false | null | "abc" |
+------+------+-------+------+-------+
|    1 |    1 |     1 |    1 |     1 |
+------+------+-------+------+-------+

Example: length of an arrays

SELECT
    JSON_LENGTH('[]') AS `[]`,
    JSON_LENGTH('[1, 2]') AS `[1, 2]`,
    JSON_LENGTH('[1, {"x": 2}]') AS `[1, {"x": 2}]`;

Output:

+------+--------+---------------+
| []   | [1, 2] | [1, {"x": 2}] |
+------+--------+---------------+
|    0 |      2 |             2 |
+------+--------+---------------+

Example: length of objects

SELECT
    JSON_LENGTH('{}') AS `[]`,
    JSON_LENGTH('{"x": 1, "y": 2}') AS `{"x": 1, "y": 2}`,
    JSON_LENGTH('{"x": 1, "y": {"z": 2}}') AS `{"x": 1, "y": {"z": 2}}`;

Output:

+------+------------------+--------------------------+
| []   | {"x": 1, "y": 2} | {"x": 1, "y": {"z": 2}} |
+------+------------------+--------------------------+
|    0 |                2 |                        2 |
+------+------------------+--------------------------+

Example: path

You can get the length of a node value specified by a path expression in a JSON document.

SELECT JSON_LENGTH('{"x": 1, "y": [1, 2]}', '$.y');

Output:

+---------------------------------------------+
| JSON_LENGTH('{"x": 1, "y": [1, 2]}', '$.y') |
+---------------------------------------------+
|                                           2 |
+---------------------------------------------+

Here, the path expression $.y has the value [1, 2], and the length of [1, 2] is 2. This is equivalent to using the JSON_EXTRACT() function extract the matching part of the path, and then calculate the length, as follows:

SELECT JSON_LENGTH(JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y'));

Output:

+-----------------------------------------------------------+
| JSON_LENGTH(JSON_EXTRACT('{"x": 1, "y": [1, 2]}', '$.y')) |
+-----------------------------------------------------------+
|                                                         2 |
+-----------------------------------------------------------+

Non-existent path

If you pass a path that does not exist in the JSON document, the MariaDB JSON_LENGTH() function will return NULL.

SELECT JSON_LENGTH('{"x": 1, "y": [1, 2]}', '$.z');

Output:

+---------------------------------------------+
| JSON_LENGTH('{"x": 1, "y": [1, 2]}', '$.z') |
+---------------------------------------------+
|                                        NULL |
+---------------------------------------------+

Conclusion

In MariaDB, JSON_LENGTH() is a built-in function that returns the length of a JSON document or a node within a JSON document specified by a path.