PostgreSQL provides a JSON array data type that helps us store ordered collections in JSON format. While JSONB arrays store the data in “JSON Binary” format. In PostgreSQL, various built-in functions are used to deal with the JSON and JSONB data efficiently. One such function is JSONB_ARRAY_LENGTH() which retrieves the length of the given JSONB array.

This post illustrates a comprehensive guide to finding the length of the given JSONB array.

How to Use in JSONB_ARRAY_LENGTH() Function in PostgreSQL

In Postgres, a built-in function named “JSONB_ARRAY_LENGTH()” is used to calculate/determine the length of a JSONB array. The retrieved length represents the total number of elements in that particular JSONB array.

Syntax

Use the below-stated syntax to retrieve the length of a JSON array using the JSON_ARRAY_LENGTH() function:

JSON_ARRAY_LENGTH(array JSON);

Parameters

The stated function accepts a single argument “array” that represents a JSONB array.

Return Value

The stated function retrieves the length of the given JSONB array.

Return Type

It retrieves an INTEGER value which indicates the length of the specified JSONB array.

Example 1: Finding the Length of a JSONB Array in Postgres

The below code snippet demonstrates the use of the JSONB_ARRAY_LENGTH() function in PostgreSQL:

SELECT JSONB_ARRAY_LENGTH('["John", "Joseph", "Mike", ["Stephen", "Seth"]]');

The stated function retrieves the length of the specified array:

Example 2: Finding the Length of a JSONB Column in PostgreSQL

Let’s set up a sample table with the following structure:

CREATE TABLE emp_table_1 (
emp_id SERIAL PRIMARY KEY,
emp_data JSONB
);

Now execute the following query to insert new records into the “emp_table”:

INSERT INTO emp_table(emp_data) 
VALUES
('[100, 120, 30, 210, 1]'),
('["John", "Joseph", "Mike", "Johnson", "Miller"]'),
('[{"e_name": "John", "e_age": 28},
{"e_name": "Johnson", "e_age": 32},
{"e_name": "Joseph", "e_age": 27}]')
RETURNING *;

The desired records have been successfully inserted into the “emp_table”:

Now we will use the JSONB_ARRAY_LENGTH() function to find the length of the “emp_data” column:

SELECT emp_data, 
JSONB_ARRAY_LENGTH(emp_data)
FROM emp_table;

The stated function successfully retrieves the length of the given JSONB array:

That’s all about finding the length of a JSONB array in PostgreSQL.

Conclusion

In PostgreSQL, a built-in function named “JSONB_ARRAY_LENGTH()” is used to determine the length of a JSONB array. The retrieved length represents the total number of elements in that particular JSONB array. The stated function accepts a single argument “array” which must be a JSONB array. This post has illustrated the working of the JSONB_ARRAY_LENGTH() function in PostgreSQL.