JSON_ARRAY_LENGTH

7min

Overview
The JSON_ARRAY_LENGTH() function returns the length of a specified JSON array.
Syntax
This function has the following basic syntax.
Syntax
JSON_ARRAY_LENGTH(arrayval JSON)
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:
Json array length
SELECT JSON_ARRAY_LENGTH('[4, 7, 10, 11, 14, {"vegetables":"spinach","fruits":"melon"}, {"a":"b"}]');
SELECT JSON_ARRAY_LENGTH('[4, 7, 10, 11, 14, {"vegetables":"spinach","fruits":"melon"}, {"a":"b"}]');
﻿
The function above will return the following result:
Output
+-------+
| f     |
+-------+
| 7     |
+-------+
+-------+
| f     |
+-------+
| 7     |
+-------+
﻿
Case #2: Get a JSON array length with a number
The following example returns the number of elements in the array.
Json array length
SELECT JSON_ARRAY_LENGTH('[1, 2, [3, 4]]');
SELECT JSON_ARRAY_LENGTH('[1, 2, [3, 4]]');
﻿
You will get the final result as follows:
Output
+-------+
| f     |
+-------+
| 3     |
+-------+
+-------+
| 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.
Json array length
SELECT JSON_ARRAY_LENGTH('[]');
SELECT JSON_ARRAY_LENGTH('[]');
﻿
An empty array will return 0 in the final output:
Output
+-------+
| f     |
+-------+
| 0     |
+-------+
+-------+
| f     |
+-------+
| 0     |
+-------+
﻿
﻿

Updated 09 Oct 2023

Did this page help you?

JSON_EXTRACT_PATH_TEXT

JSON_ARRAY_EXTRACT

Docs powered by Archbee

TABLE OF CONTENTS

Overview

Syntax

Examples

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

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

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