PostgreSQL provides a JSON array data type that helps us store ordered collections in JSON format. JSON arrays are similar to standard arrays in programming. They are enclosed within square brackets “[ ]” and can have different data types like strings, objects, numbers, etc. JSON arrays are optimized for PostgreSQL's database engine.

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

How to Find/Fetch the JSON Array Length in Postgres?

In Postgres, a built-in function named “JSON_ARRAY_LENGTH()” is used to calculate/determine the length of a JSON array. The retrieved length represents the total number of elements in that particular JSON 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 JSON array.

Return Value

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

Return Type

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

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

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

SELECT JSON_ARRAY_LENGTH('[100, 110, 90, [120, -12]]');

The stated function retrieves the length of the specified array:

Example 2: Finding the Length of a JSON Column in Postgres

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

CREATE TABLE emp_table (
emp_id SERIAL PRIMARY KEY,
emp_data JSON
);

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}]');

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

Confirm the table’s content by executing the following command:

SELECT * FROM emp_table;

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

SELECT emp_data, 
JSON_ARRAY_LENGTH(emp_data)
FROM emp_table;

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

That’s all about finding the length of a JSON array in Postgres.

Conclusion

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