Postgres JSON Array Length

PostgreSQL provides us with the JSON array, a data type that allows us to store an ordered collection of elements in a JSON format. JSON arrays are similar to regular arrays in other programming languages but are specifically designed to work within the PostgreSQL database engine.

We represent the JSON arrays as a single value with the elements inside a pair of square brackets. The elements inside the arrays are separated by a comma where each element can be any valid JSON data type such as a string, numbers, booleans, objects, and nested arrays.

In this tutorial, we will learn how we can use the PostgreSQL json_array_length() to determine the length of a given JSON array.

PostgreSQL Json_array_length()

The json_array_length() function in PostgreSQL allows us to return the number of elements in a JSON array. The function takes the JSON array as an argument and returns an integer that denotes the array length.

First Steps

Ensure that PostgreSQL is installed and running on your system. If you don’t have it installed, check our tutorials on setting up PostgreSQL on your target system.

The next step is to create a new PostgreSQL database or use an existing one.

Finally, connect to the PostgreSQL database using a client of your choice such as the psql or a graphical interface like pgAdmin.

Setup the Sample Table

Let us create a table that we can use to demonstrate the usage of the json_array_length() function.

CREATE TABLE json_table (

id serial PRIMARY KEY,

data json

);

The previous query should create a new table called “json_table” with an id column that stores the integer values and a data column that holds the JSON array values.

We can then insert some sample JSON data into the table using the INSERT statements as shown in the following:

INSERT INTO json_table (data) VALUES
('[1, 2, 3]'),
('["apple", "banana", "cherry"]'),
('[{"name": "John", "age": 30}, {"name": "Jane", "age": 25}]');

This should include a JSON array in the table.

Fetch the Array Length

To retrieve the length of the input JSON array, we can query the table and pass the value to the json_array_length() as shown in the following statement:

SELECT data, json_array_length(data) AS array_length

FROM json_table;

The given query returns the JSON data that is stored in the table and the length of each JSON array.

An example output is as follows:

There you have it! A way to determine the length of a JSON array in PostgreSQL using the json_array() function.

NOTE: The json_array_length() function only works with a valid JSON array. Hence, the query returns an error if the input argument to the function is not a valid JSON array. It is, therefore, essential to ensure that the input data is correctly formatted to a valid JSON.

Conclusion

We started by exploring what a PostgreSQL JSON array is, its uses, and how we can create a table that stores a valid JSON. We finally discussed how we could use the json_array_length() function in PostgreSQL to determine the length of an input array.