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'
  ) 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)

Summary

• Use the JSON_LENGTH() function to determine the length of a JSON document, which is the number of elements in a JSON array or the number of key-value pairs in a JSON object.