The element itself may be nullable. Counting its nonnull values answers a different question from counting its positions, so include that distinction in the request.
Make the empty array part of the fixture
This original fictional fixture requires one output row per unique order_id. Count the number of positions in items, including a position whose value is null:
WITH orders AS (
SELECT 'A' AS order_id, [10, 20] AS items
UNION ALL
SELECT 'B', ARRAY<INT64>[]
UNION ALL
SELECT 'C', ARRAY<INT64>[NULL]
)
SELECT
o.order_id,
COUNT(pos) AS item_count,
COUNT(item) AS nonnull_value_count
FROM orders AS o
LEFT JOIN UNNEST(o.items) AS item WITH OFFSET AS pos ON TRUE
GROUP BY o.order_id
ORDER BY o.order_id;
The expected rows are:
| order_id | item_count | nonnull_value_count |
|---|---|---|
| A | 2 | 2 |
| B | 0 | 0 |
| C | 1 | 0 |
These are authored query expectations, not an observed BigQuery run. The nullable element is an intermediate expression in this teaching query; the query returns only scalar counts. Do not infer that every nullable array expression can be persisted or returned unchanged in a final result.
Identify what each count measures
Google's BigQuery array documentation, checked October 24, 2026, describes flattening arrays with UNNEST and preserving empty-array source rows with a LEFT JOIN. For B, the join retains one placeholder row with no element offset. COUNT(*) would count that row and incorrectly return 1 for the requested item count.
For C, there is a real array position: its offset is 0 even though its value is null. COUNT(pos) therefore returns 1, while COUNT(item) returns 0. Keeping both measures in a diagnostic query exposes that distinction.
An INNER or CROSS JOIN would drop B entirely. That is appropriate for some element-only reports, but it fails this fixture's requirement that every order remain visible. Also preserve the unique-parent assumption: if order_id repeats in the source, grouping only by that field combines multiple parent records.
Ask for a parent-to-element trace
Write a read-only BigQuery GoogleSQL diagnostic.
Actual table schema, repeated-field type and parent identity: [PASTE]
Required output grain and count definition: [ENTER]
Permitted sample rows and observed wrong output: [PASTE]
Null-element and empty-array policies: [ENTER]
For this fixture, unique orders A, B and C have items [10,20], [] and [NULL].
Retain all three parents. Count array positions, including null-valued
positions; report nonnull values separately. Expected counts are
A: 2/2, B: 0/0, C: 1/0. Trace the rows produced by LEFT JOIN UNNEST and explain
COUNT(*), COUNT(item) and COUNT(offset). Preserve raw parents and values.
Do not filter away placeholder rows, silently deduplicate parents,
invent schema fields, run a job or claim measured query performance.
Run the small SELECT in an approved test context and inspect both the IDs and counts before adapting the table names. The SQL counting guide addresses the separate problem of repeating parent amounts across relational line records. Here, accept the repeated-field query after the empty parent survives and a null-valued position remains distinguishable from no position at all.