In DuckDB, the json_array_length() function is used to determine the number of elements in a JSON array. This function can return the count of items in the top-level array or in a nested array within it. It’s especially useful when working with complex JSON structures where arrays are embedded inside objects or other arrays. By combining it with JSON path expressions, you can target specific portions of the data for more precise analysis.

Syntax

json_array_length(json[, path])	

Parameters:

• json: The main JSON document (as a string) in which to search.
• path: Optional path to search within the JSON document.

Example

Here’s a basic example that demonstrates how it works:

SELECT json_array_length('[1, 2, 3, 4]');

Result:

4

Here’s an array of strings:

SELECT json_array_length('["Cat", "Dog", "Chimp"]');

Result:

3

Nested Arrays

We can use a second argument to tell json_array_length() to search a specified array within the array. For example:

SELECT json_array_length('[[1, 2], [3, 4, 5], [6, 7, 8, 9]]', '$[2]');

Result:

4

Arrays are zero-based, and so $[2] specifies the third array within the outer array.

Arrays Within a JSON Object

We’re not limited to just searching arrays within other arrays. We can also use JSON path to specify an array within a JSON object:

SELECT json_array_length('{ 
    "name": "Kerry",
    "scores": [12, 15, 21]
}', '$.scores');

Result:

3

And the array can be nested within another array, within an outer JSON document:

SELECT json_array_length('{ 
    "name": "Kerry",
    "scores": [[12, 15, 21], [8, 7, 3, 5], [9, 15]]
}', '$.scores[1]');

Result:

4

Non-Arrays

If we specify a value that’s not a JSON array, then the function returns 0:

SELECT json_array_length('"Bruce"');

Result:

0

Actually, that’s on the assumption that the value is a valid JSON value. If we pass a non-JSON value, we’ll likely get an error. For example, if I remove the double quotes:

SELECT json_array_length('Bruce');

Result:

Invalid Input Error:
Malformed JSON at byte 0 of input: unexpected character.  Input: Bruce

So it was still expecting the value to be a valid JSON value, even if it wasn’t a JSON array. To be clear, JSON requires strings to be surrounded in double quotes. In that second example, I removed the double quotes, which meant that it no longer conforms to the JSON convention.