How to Get Array Length in BigQuery
Last updated August 23, 2026 · By the SaturnSQL team
ARRAY_LENGTH(array) returns the number of elements. It gives NULL for a NULL array and 0 for an empty one, so use IFNULL when you need a number.
SELECT id, ARRAY_LENGTH(tags) AS tag_count
FROM articles;NULL arrays versus empty arrays
BigQuery distinguishes an array that is NULL from an array with no elements, and ARRAY_LENGTH reflects that: NULL in, NULL out. Aggregations then skip those rows, which is usually not what a count is meant to show.
SELECT id, IFNULL(ARRAY_LENGTH(tags), 0) AS tag_count
FROM articles
ORDER BY tag_count DESC;Filtering on length
ARRAY_LENGTH(arr) = 0 finds rows where the array exists but is empty; arr IS NULL finds rows where it was never set. Checking for both takes two predicates.
SELECT COUNT(*) AS untagged
FROM articles
WHERE tags IS NULL OR ARRAY_LENGTH(tags) = 0;Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free