json_length

Returns the length of a JSON document. If a path is specified, this function returns the length of the value identified by the path.

tip

All of the JSON functions and operators are listed in the navigation and on the overview page

Accelerate your queries with generated columns

The length of a document is determined according to the following rules:

• The length of a scalar value is 1. For example, the length of 1, "a", true, false, and 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 {"a": 1} is 1.

• The length of nested arrays or objects does not count. For example, the length of {"a": [1, 2]} is 1, because the nested array [1, 2] is not calculated into length.

Syntax

json_length(json_doc[, path])

Parameters

json_doc: required, the JSON document for which to return the length.

path: optional. It is used to return the length of a value within the document. The path generally starts with $ and uses . as the path separator. [] is used as the array subscript, which starts from 0.

Return value

Returns a value of the INT type.

An error is returned if the JSON document is not a valid document.

0 is returned in any of the following scenarios:

• The path does not identify a value in the document.

• The path is not a valid path expression.

• The path contains the * or ** wildcard.

Examples

Example 1: Return the length of a scalar value.

select json_length('1');
+------------------+
| json_length('1') |
+------------------+
|                1 |
+------------------+

Example 2: Return the length of an empty object.

select json_length('{}');
+-------------------+
| json_length('{}') |
+-------------------+
|                 0 |
+-------------------+

Example 3: Return the length of an object that has data.

select json_length('{"Name": "Homer"}');
+----------------------------------+
| json_length('{"Name": "Homer"}') |
+----------------------------------+
|                                1 |
+----------------------------------+

Example 4: Return the length of a JSON array.

select json_length('[1, 2, 3]');
+--------------------------+
| json_length('[1, 2, 3]') |
+--------------------------+
|                        3 |
+--------------------------+

Example 5: Return the length of a JSON array in which one element has a nested array.

The nested array [3, 4] is not calculated into length.

select json_length('[1, 2, [3, 4]]');
+-------------------------------+
| json_length('[1, 2, [3, 4]]') |
+-------------------------------+
|                             3 |
+-------------------------------+

Example 6: Return the length of an object specified by path $.Person.

SET @file = '{  
    "Person": {    
       "Name": "Homer", 
       "Age": 39,
       "Hobbies": ["Eating", "Sleeping"]  
    }
 }';
select json_length(@file, '$.Person') 'Result';

Example 7: Return the length of the value specified by path $.y.

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