Overview

The JSON_ARRAY_LENGTH() function returns the length of a specified JSON array.

Syntax

This function has the following basic syntax.
JSON_ARRAY_LENGTH(arrayval JSON)
The required argument for this function is arrayval. It represents the JSON array which we will count the length.

Examples

Case #1: Get a JSON array length with a JSON value

The following example returns the number of elements in the array:
SELECT JSON_ARRAY_LENGTH('[4, 7, 10, 11, 14, {"vegetables":"spinach","fruits":"melon"}, {"a":"b"}]');
The function above will return the following result:
+-------+
| f     |
+-------+
| 7     |
+-------+

Case #2: Get a JSON array length with a number

The following example returns the number of elements in the array.
SELECT JSON_ARRAY_LENGTH('[1, 2, [3, 4]]');
You will get the final result as follows:
+-------+
| f     |
+-------+
| 3     |
+-------+

Case #3: JSON array length where the array is NULL or empty

This example shows that an empty JSON array will return 0.
SELECT JSON_ARRAY_LENGTH('[]');
An empty array will return 0 in the final output:
+-------+
| f     |
+-------+
| 0     |
+-------+