SQLite json_array_length() Function

The SQLite json_array_length() function returns the length of elements in the specified JSON array, that is the number of the top level child elements in the JSON array.

Syntax

This is the syntax of the SQLite json_array_length() function:

json_array_length(json_array)

or

json_array_length(json_doc, path)

Parameters

json_array

Required. A text representing a JSON array.

json_doc

Required. A text representing a JSON doc.

path

Required. The path in the JSON doc is a JSON array.

Return value

The SQLite json_array_length(json_array) function with one parameter returns the number of elements in json_array.

The SQLite json_array_length(json_array, path) function with two parameters returns the number of elements in the array specified by path path in a JSON document json_doc.

Examples

This example shows how to use the SQLite json_array_length() function to get the number of elements in [1, 2, [3, 4]].

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

json_array_length('[1, 2, [3, 4]]')
-----------------------------------
3

Here, the array [1, 2, [3, 4]] contains 3 elements, so the json_array_length() function returns 3.

You can get the number of elements in the inner array [3, 4] in [1, 2, [3, 4]] by path $[2]:

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

json_array_length('[1, 2, [3, 4]]', '$[2]')
-------------------------------------------
2

Let’s look at a few other examples:

SELECT
    json_array_length('[1,2,3,4]'),
    json_array_length('[1,2,3,4]', '$'),
    json_array_length('[1,2,3,4]', '$[2]'),
    json_array_length('{"one":[1,2,3]}'),
    json_array_length('{"one":[1,2,3]}', '$.one'),
    json_array_length('{"one":[1,2,3]}', '$.two');

               json_array_length('[1,2,3,4]') = 4
          json_array_length('[1,2,3,4]', '$') = 4
       json_array_length('[1,2,3,4]', '$[2]') = 0
         json_array_length('{"one":[1,2,3]}') = 0
json_array_length('{"one":[1,2,3]}', '$.one') = 3
json_array_length('{"one":[1,2,3]}', '$.two') =