Introduction to the PostgreSQL jsonb_array_length() function

Thejsonb_array_length() function returns the number of elements in the top-levelJSON array.

Here’s the syntax of thejsonb_array_length() function:

jsonb_array_length(json_array)

In this syntax, you pass a JSON array with the type JSONB to the function. It’ll return the number of elements in the array.

If the array is empty, thejsonb_array_length() function returns zero. If the argument is not an array, the function will issue an error. In case thejson_array isNULL, thejsonb_array_length() function will returnNULL.

Note that the function will return the number of elements of the top-level array only. If the array contains nested arrays, the function will not count the elements in the nested arrays but consider the nested arrays as individual elements.

PostgreSQL jsonb_array_length() function examples

Let’s explore some examples of using thejsonb_array_length() function.

1) Basic PostgreSQL jsonb_array_length() function example

The following example uses thejsonb_array_length() function to get the number of elements in a JSON array:

SELECT jsonb_array_length('[1,2,3]');

Output:

jsonb_array_length--------------------                  3(1 row)

The function returns 3 because the JSON array [1,2,3] contains three elements.

2) Using the jsonb_array_length() function with nested arrays

The following example uses thejsonb_array_length() function with an array that contains another array:

SELECT jsonb_array_length('[1,2,3, [4,5], 6]');

Output:

jsonb_array_length--------------------                  5(1 row)

In this example, the function returns 5 because the top-level array contains 5 elements: 1, 2, 3, an array [4,5], and 6.

3) Using the jsonb_array_length() function with table data

First,create a table calledperson:

CREATE TABLE person (    idSERIAL PRIMARY KEY,    info JSONB);

In thisperson table, theinfo column has the typeJSONB that contains the person’s information including name, age, and pets.

Second,insert some rows into theperson table:

INSERT INTO person (info)VALUES    ('{"name": "Alice", "age": 30, "pets": [{"type": "cat", "name": "Fluffy"}, {"type": "dog", "name": "Buddy"}]}'),    ('{"name": "Bob", "age": 35, "pets": [{"type": "dog", "name": "Max"}]}'),    ('{"name": "Charlie", "age": 40, "pets": [{"type": "rabbit", "name": "Snowball"}]}')RETURNING*;

Third, retrieve the person names with their number of pets from theinfo column of theperson table:

SELECT  jsonb_path_query(info,'$.name')name,  jsonb_array_length(    jsonb_path_query(info,'$.pets')  ) pet_countFROM  person;

Output:

name    | pet_count-----------+----------- "Alice"   |         2 "Bob"     |         1 "Charlie" |         1(3 rows)

In this example:

• Thejsonb_path_query(info, ‘$.name’) returns the name of the person.
• Thejsonb_path_query(info, ‘$.pets’) returns thepets array, andjsonb_array_length() returns the number of elements in thepets array.

Summary

• Use thejsonb_array_length() function to get the number of elements in the top-level JSON array.

Last updated onFebruary 24, 2024

On this page

• Introduction to the PostgreSQL jsonb_array_length() function
• PostgreSQL jsonb_array_length() function examples
• Summary