JSON_ARRAY_LENGTH

Return the number of elements in a JSON array.

Category: jsonReturns: INTEGERDialect: PostgreSql

Syntax

JSON_ARRAY_LENGTH(json)

Description

## Overview Returns the number of elements in the outermost JSON array. Use this function to compute array cardinality for filtering, sorting, or feature extraction on columns that store arrays as JSON text. This function inspects only the top-level array; nested arrays contribute one element each to the count, not their own lengths. For the binary-JSON counterpart, use JSONB_ARRAY_LENGTH. ## Behavior - Returns an INTEGER equal to the element count of the outer array. - Returns 0 for an empty array ('[]'). - Returns NULL for NULL input. - Raises an error if the input is valid JSON but not an array (for example a JSON object or scalar). - Raises an error if the input string is not valid JSON. - The count is unaffected by element types; objects, arrays, nulls, and scalars all count as one element. ## Compatibility - Follows the SQL/JSON standard (SQL:2016) array-length semantics. - JSON parsing follows RFC 8259.

Parameters

Name	Type	Description
`json`		Specifies a JSON value that must be an array. If the value is NULL, the function returns NULL. If the value is valid JSON but is not an array (for example an object or scalar), the function raises an error.

Examples

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

-- Empty array returns 0
SELECT JSON_ARRAY_LENGTH('[]') AS len;

-- Mixed-type array is counted by elements, not type
SELECT JSON_ARRAY_LENGTH('["a", 1, true, null, {"k": "v"}]') AS len;

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

-- Count tags per product from a realistic table
SELECT product_id, JSON_ARRAY_LENGTH(tags_json) AS tag_count
FROM ecommerce.catalog.products;

-- Guard against non-array values with JSON_TYPEOF
SELECT item_id,
       CASE WHEN JSON_TYPEOF(attrs) = 'array' THEN JSON_ARRAY_LENGTH(attrs) ELSE 0 END AS n
FROM inventory.warehouse.items;

Pitfalls

Raises an error on non-array JSON, which will fail the whole query. Guard with JSON_TYPEOF = 'array' when the column can contain mixed shapes.
Only the outer array is measured. '[[1,2],[3,4,5]]' returns 2, not 5.
Malformed JSON raises a parse error; combine with a COALESCE/TRY pattern if your data has a small fraction of bad rows.
Returns NULL for SQL NULL input, but 0 for the empty array '[]'. Treat these cases distinctly in analytics queries.