In SQLite, the json_array_length()
function returns the number of elements in a given JSON array.
The array is provided as an argument. If the array is embedded within a larger JSON document, we can use an optional second argument to specify the path to the array.
If the value is not an array, the function returns 0
.
Syntax
The function can be used in the following two ways:
json_array_length(X)
json_array_length(X,P)
Where X
represents the array and P
is an optional path that can be used to specify the path to the array within a larger document.
Example
Here’s a basic example to demonstrate:
SELECT json_array_length('[ 7, 12, 10 ]');
Result:
3
The array contains three elements and so 3
is returned.
Specify a Path
We can also use a second argument in order to specify the path to the array:
SELECT json_array_length('[ 7, 12, 10 ]', '$');
Result:
3
In this case the array is at the top level, and so we pass $
as the path.
The following example uses an array that’s embedded within a larger document:
SELECT json_array_length('{
"dogs" : [
{ "name" : "Wag", "scores" : [ 7, 9 ] },
{ "name" : "Bark", "scores" : [ 3, 4, 8, 7 ] },
{ "name" : "Woof", "scores" : [ 3, 2, 1 ] }
]
}',
'$.dogs'
);
Result:
3
In this case, the array at dogs
contains three elements.
We can navigate down to the next level and find the number of elements in one of the other arrays:
SELECT json_array_length('{
"dogs" : [
{ "name" : "Wag", "scores" : [ 7, 9 ] },
{ "name" : "Bark", "scores" : [ 3, 4, 8, 7 ] },
{ "name" : "Woof", "scores" : [ 3, 2, 1 ] }
]
}',
'$.dogs[1].scores'
);
Result:
4
Arrays are zero based, and so the count starts at 0
. Therefore, we specified [1]
to get the second element within the dogs
array, which happens to be a JSON object. We then use .scores
to select its array.
Selecting Non-Arrays
If the path points to a value other than a JSON array, 0
is returned:
SELECT json_array_length('{
"dogs" : [
{ "name" : "Wag", "scores" : [ 7, 9 ] },
{ "name" : "Bark", "scores" : [ 3, 4, 8, 7 ] },
{ "name" : "Woof", "scores" : [ 3, 2, 1 ] }
]
}',
'$.dogs[1].name'
);
Result:
0
Selecting a Non-Existent Path
If the second argument points to a path that doesn’t exist, null
is returned.
First, let’s set .nullvalue
to NULL
:
.nullvalue NULL
The .nullvalue
dot command allows us to provide a string that will be used to replace null values. It’s one of several ways you can replace null values with a string in SQLite. In this case, I set it to NULL
. Now, any null values will return NULL
instead of a blank result.
Now let’s call json_array_length()
, but use a second argument that points to a non-existent path:
SELECT json_array_length('{
"dogs" : [
{ "name" : "Wag", "scores" : [ 7, 9 ] },
{ "name" : "Bark", "scores" : [ 3, 4, 8, 7 ] },
{ "name" : "Woof", "scores" : [ 3, 2, 1 ] }
]
}',
'$.cats'
);
Result:
NULL
Invalid Paths
We’ll get an error if our path is not well-formed:
SELECT json_array_length('{
"dogs" : [
{ "name" : "Wag", "scores" : [ 7, 9 ] },
{ "name" : "Bark", "scores" : [ 3, 4, 8, 7 ] },
{ "name" : "Woof", "scores" : [ 3, 2, 1 ] }
]
}',
'dogs'
);
Result:
Runtime error: JSON path error near 'dogs'
In this case, I forgot to include $.
at the front of the path.
Invalid JSON Documents
We’ll also get an error the JSON is not well-formed:
SELECT json_array_length('{ "Dogs" : }',
'$'
);
Result:
Runtime error: malformed JSON
This time the error tells us that our JSON is malformed.