jsonb_array_length in PostgreSQL: Counting JSON Array Elements Safely

The jsonb_array_length function answers one question: how many elements are in a JSON array. It sounds trivial, but in practice it happily fails with cannot get array length of a non-array the moment an object, a number, or a null shows up in the column. Let us cover how to count the length safely, filter rows by array size, and how it differs from cardinality on a native SQL array.

Counting elements, basics

Suppose orders have a data jsonb column where the items key holds an array of cart line items. data->'items' returns the JSONB array itself (the -> arrow, not ->>), and jsonb_array_length takes its length:

SELECT id,
       jsonb_array_length(data->'items') AS item_count
FROM orders;

A few facts that save time:

• The argument must be a JSONB array ([...]), not an object or a scalar.
• Length is counted at the top level only: a nested array counts as one element.
• An empty array [] yields 0 -- a valid answer, not an error.
• For the json type (without b) there is a twin, json_array_length.

The non-array error and how to guard it

The main pain shows up when items is missing in some rows or stores something other than an array. Then the whole query blows up:

-- ERROR: cannot get array length of a non-array
SELECT jsonb_array_length(data->'items') FROM orders;

There are three causes, and all appear in real data:

• The items key is absent -- the arrow returns SQL NULL, and here there is no error, you just get NULL.
• items holds an object {...} or a string -- that is what crashes the function.
• The value is JSON null ('null'::jsonb), which is also not an array.

The reliable guard is to check the type with jsonb_typeof before calling length. The cleanest move is to wrap it in a CASE or pair it with a filter:

SELECT id,
       CASE
         WHEN jsonb_typeof(data->'items') = 'array'
           THEN jsonb_array_length(data->'items')
         ELSE 0
       END AS item_count
FROM orders;

Gotcha: in PostgreSQL WHERE and SELECT do not guarantee evaluation order, so WHERE jsonb_typeof(...) = 'array' AND jsonb_array_length(...) > 2 could in theory try to compute the length before the type check. In practice the planner usually puts the cheap check first, but a CASE or a subquery with an explicit order is safer. Do not rely on AND short-circuiting to suppress side-effect errors.

Filtering rows by array size

A typical task is to find orders with a "large" cart. First exclude non-arrays, then compare the length:

SELECT id, user_id, amount
FROM orders
WHERE jsonb_typeof(data->'items') = 'array'
  AND jsonb_array_length(data->'items') >= 3;

The same trick works for aggregates. For example, the average cart size over valid orders only:

SELECT avg(jsonb_array_length(data->'items')) AS avg_items
FROM orders
WHERE jsonb_typeof(data->'items') = 'array';

If the array sits deeper, extend the path with arrows; the type guard stays the same:

SELECT u.id, u.email
FROM users AS u
WHERE jsonb_typeof(u.data->'profile'->'tags') = 'array'
  AND jsonb_array_length(u.data->'profile'->'tags') = 0;

This selects users whose tags are exactly zero (an empty array), but specifically an array, not a missing key.

jsonb_array_length versus cardinality

Do not confuse a JSON array with a native PostgreSQL array like text[] or int[]. They use different length functions:

• jsonb_array_length(x) -- for a JSONB array value.
• cardinality(x) -- for a SQL array; returns the total number of elements.
• array_length(x, 1) -- the SQL array length along the first dimension; on an empty array it returns NULL, not 0.

-- SQL array stored in a real array column
SELECT cardinality(roles) AS role_count
FROM employees;          -- assumes roles text[]

-- JSON array stored inside a jsonb column
SELECT jsonb_array_length(data->'items') AS item_count
FROM orders;

Rule of thumb: if data arrives as a document and the shape is fluid, that is JSONB -- count with jsonb_array_length guarded by jsonb_typeof. If the array is uniform and filtered often, it is a candidate for a native array column with cardinality, which is terser and never trips on scalars.

Other engines differ, so do not assume the same semantics. In MySQL the JSON array length comes from JSON_LENGTH(data, '$.items'), and it does not raise the non-array error at all -- but it is not measuring an array length either. On a scalar (a number, a string, a boolean) JSON_LENGTH returns 1, and on an object it returns the number of top-level keys, not "the length of the value". So a row where items is {"a":1,"b":2} quietly yields 2, and a row where it is 5 yields 1 -- no error, but also not a cart size. In ClickHouse you use JSONLength(data, 'items') for JSON, or work with native Array(...) columns and the length() function. Port queries between engines deliberately -- behavior on a non-array and on a missing key differs in each.