JSON_ARRAY_LENGTH

This article will show you how the JSON_ARRAY_LENGTH() function returns the length of a JSON array.

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     |
+-------+