MySQL JSON_LENGTH() Function

In MySQL, the JSON_LENGTH() function returns the length of the JSON document or the specified path.

`JSON_LENGTH()` Syntax

Here is the syntax of the MySQL JSON_LENGTH() function:

JSON_LENGTH(json)
JSON_LENGTH(json, path)

Parameters

json: Required. The JSON document.
path: Optional. The path expression.

Return value

If specified path, the JSON_LENGTH() function returns the length of the data matching the path in the JSON document, otherwise it returns the length of the JSON document. The function calculates the length of the JSON document according to the following rules:

The length of a scalar is 1. For example, the length of 1, '"x"', true, false, null is all 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.

The JSON_LENGTH() function will return NULL in the following cases:

If the specified path does not exist in the JSON document.
If any parameter is NULL.

There will happen an error in the following cases:

MySQL will give an error if the parameter json is not a valid JSON document. You can use JSON_VALID() to verify the JSON document.
If the parameter path is not a valid path expression, MySQL will give an error.
Before MySQL 8.0.26, if path the parameter contained * or **, MySQL would give an error.

`JSON_LENGTH()` Examples

Here are some examples of JSON_LENGTH().

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"`;

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

Length of arrays

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

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

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}}`;

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

Length of a path

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

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

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

Here, the path $.y corresponds to [1, 2], and the length of [1, 2] is 2. This is equivalent to using JSON_EXTRACT() to extract path matched the path first, and then calculate the length, as follow:

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

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

JSON_LENGTH() Syntax