SQLite JSON_ARRAY

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:

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:

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:

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:

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:

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.