MySQL JSON_LENGTH() Function

Summary : in this tutorial, you will learn how to use the MySQL JSON_LENGTH() function to get the length of a JSON document.

Introduction to MySQL JSON_LENGTH() function

The JSON_LENGTH() function returns the length of a JSON document. Here’s the syntax of the JSON_LENGTH() function:

JSON_LENGTH(json_doc[, path])Code language: SQL (Structured Query Language) (sql)

In this syntax:

json_doc : This is the JSON document you want to get the length.
path : This is an optional argument. If you provide the path , the JSON_LENGTH() function will return the length of the value within the json_doc located by the path.

The JSON_LENGTH() function returns NULL if any argument is NULL or the path does not identify any value in the JSON document.

The JSON_LENGTH() function raises an error if the json_doc is not a valid JSON document or the path is not a valid path expression.

The JSON_LENGTH calculates the length of a JSON document as follows:

A scalar has a length of 1.
An array has a length of the number of array elements.
An object has the length of the number of object members.

The function doesn’t consider the length of nested arrays or objects.

MySQL JSON_LENGTH() function example

Let’s take some examples of using the JSON_LENGTH() function.

1) Getting the length of a scalar

The following example uses the JSON_LENGTH() function to return the length of a scalar:

SELECT 
  JSON_LENGTH('"Hello"') length;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| length |
+--------+
|      1 |
+--------+
1 row in set (0.01 sec)Code language: SQL (Structured Query Language) (sql)

2) Getting the length of an array

The following example uses the JSON_LENGTH() function to return the number of elements in an array:

SELECT 
  JSON_LENGTH('[1, 2, 3]') length;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| length |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

3) Getting the length of an object

The following example uses the JSON_LENGTH() function to return the number of members in an object:

SELECT 
  JSON_LENGTH('{"name": "John", "age": 22}') length;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| length |
+--------+
|      2 |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

4) Getting the length of an object that has a nested array

The following example uses the JSON_LENGTH() function to return the number of members in an object that has a property which is an array. It doesn’t count the length of the nested array.

SELECT 
  JSON_LENGTH(
    '{"name": "John", "age": 22, "skills":["PHP","MySQL","JavaScript"]}'
  ) length;Code language: SQL (Structured Query Language) (sql)

Output:

+--------+
| length |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

5) Getting the length of a value specified by a path

The following example uses the JSON_LENGTH() function to return the number of elements of an array identified by a path:

SELECT 
  JSON_LENGTH(
    '{"name": "John", "age": 22, "skills":["PHP","MySQL"]}',
    '$.skills'