In MySQL, the JSON_LENGTH() function returns the length of a JSON document.

When you call this function, you provide the JSON document as an argument. You can also provide a path argument to return the length of a value within the document.

Syntax

The syntax goes like this:

JSON_LENGTH(json_doc[, path])

Where json_doc is the JSON document for which to return the depth, and path is an optional argument that can be used to return the length of a value within the document.

Example 1 – Empty Object

Here’s a JSON document that contains nothing but an empty object.

SELECT JSON_LENGTH('{}') 'Result';

Result:

+--------+
| Result |
+--------+
|      0 |
+--------+

Example 2 – Object with Data

And here’s what happens if we add data.

SELECT JSON_LENGTH('{"Name": "Homer"}') 'Result';

Result:

+--------+
| Result |
+--------+
|      1 |
+--------+

This returns 1 because the object contains one member. If we add a second member, here’s what happens:

SELECT JSON_LENGTH('{"Name": "Homer", "Stupid": true}') 'Result';

Result:

+--------+
| Result |
+--------+
|      2 |
+--------+

Example 3 – Arrays

The length of an array is the number of elements it contains.

SELECT JSON_LENGTH('[1, 2, 3]') 'Result';

Result:

+--------+
| Result |
+--------+
|      3 |
+--------+

This is true, even if one of the elements contains its own array (or object).

SELECT JSON_LENGTH('[1, 2, [3, 4]]') 'Result';

Result:

+--------+
| Result |
+--------+
|      3 |
+--------+

Example 4 – The path Argument

We can provide an optional path argument to return the length of a given path within the document.

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

Result:

+--------+
| Result |
+--------+
|      3 |
+--------+

In this case we return the length of the Person object, which contains three members.

If we narrow it down to just one of those members (say, the Name member), we get this:

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

Result:

+--------+
| Result |
+--------+
|      1 |
+--------+

But if we narrow it down to the Hobbies array, we get this:

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

Result:

+--------+
| Result |
+--------+
|      2 |
+--------+