JSONB_ARRAY_LENGTH

Return the number of elements in a JSONB array.

Category: jsonReturns: INTEGERDialect: PostgreSql

Syntax

JSONB_ARRAY_LENGTH(jsonb)

Description

## Overview Returns the number of elements in the outermost JSONB array. Use this function on JSONB columns where you need array cardinality for filters, sorts, or analytics (for example 'rows with more than five tags'). Only the top-level array is measured; nested arrays count as a single element each. For the text-JSON variant, use JSON_ARRAY_LENGTH. ## Behavior - Returns an INTEGER equal to the element count of the outer array. - Returns 0 for an empty array ('[]'::JSONB). - Returns NULL for NULL input. - Raises an error if the input is valid JSONB but not an array. - Counts elements regardless of their types; objects, arrays, nulls, and scalars each contribute 1. ## Compatibility - Follows the SQL/JSON array-length semantics from SQL:2016. - JSONB canonical form is used for storage; array length is unaffected by serialization.

Parameters

Name	Type	Description
`jsonb`		Specifies a JSONB value that must be an array. Returns NULL for NULL input. Raises an error if the value is valid JSONB but not an array.

Examples

-- Count elements in a JSONB array literal
SELECT JSONB_ARRAY_LENGTH('[1, 2, 3, 4]'::JSONB) AS len;

-- Empty array returns 0
SELECT JSONB_ARRAY_LENGTH('[]'::JSONB) AS len;

-- Mixed-type elements each count as 1
SELECT JSONB_ARRAY_LENGTH('["text", 42, true, null]'::JSONB) AS len;

-- NULL input returns NULL
SELECT JSONB_ARRAY_LENGTH(NULL) AS len;

-- Realistic per-row cardinality from a table
SELECT user_id, JSONB_ARRAY_LENGTH(preferences) AS n_prefs
FROM iam.directory.user_profiles;

-- Guard against non-array JSONB to avoid errors
SELECT user_id,
       CASE WHEN JSONB_TYPEOF(attrs) = 'array' THEN JSONB_ARRAY_LENGTH(attrs) ELSE 0 END AS n
FROM iam.directory.user_profiles;

Pitfalls

Raises an error on non-array JSONB. Guard with JSONB_TYPEOF = 'array' when columns may have mixed shapes.
Only the outer array is measured; nested arrays contribute one element each to the count, not their length.
Returns NULL for SQL NULL but 0 for the empty array. Treat the two cases distinctly.
JSONB is parsed once at ingest, so repeated length queries on the same column are cheap compared with JSON_ARRAY_LENGTH on a string column.