Cloud

json_array_length

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 for which to count the length.

Examples

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"}]');

This function returns the following result:

+-------+
| f     |
+-------+
| 7     |
+-------+

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

The query returns:

+-------+
| f     |
+-------+
| 3     |
+-------+

JSON array length where the array is NULL or empty

This example shows that an empty json array returns 0.

SELECT JSON_ARRAY_LENGTH('[]');

An empty array returns 0:

+-------+
| f     |
+-------+
| 0     |
+-------+