Create a multi-dimensional array filled with a single value.
ARRAY_FILL(value, dims)## Overview Returns an array of the requested shape with every element initialised to the supplied value. ARRAY_FILL is the canonical constructor for fixed-size zero or sentinel vectors and matrices, and it is often used to bootstrap feature vectors, allocate accumulators, or materialise a default array for missing rows in a LEFT JOIN. Typical uses include constructing per-row zero vectors that downstream window functions mutate, creating placeholder matrices for tests, and producing deterministic default arrays when upstream data is NULL. ## Behavior - Returns an ARRAY (or multi-dimensional ARRAY<ARRAY<...>> for higher-rank dims) whose every element is the fill value. - The shape is determined entirely by the dims argument. A dims value of ARRAY[3] produces a flat three-element array; ARRAY[2, 3] produces a 2-row by 3-column rectangular matrix. - Element type is inferred from the value argument. To fill with NULL, cast the NULL to the desired type (for example CAST(NULL AS INT)). - Any dimension of zero produces an empty array for that axis. - Negative dimension sizes are rejected with an error. - The function never mutates arguments. ## Null and empty handling - NULL value argument produces an array whose elements are all NULL (the element type is inferred from the cast). - NULL dims argument raises an error; the shape must be explicit. - A dims element of zero yields an empty array along that axis. - An empty dims array (ARRAY() with no sizes) raises an error because the rank is undefined. ## Compatibility - Matches the standard-compatible ARRAY_FILL with dims specified as an array of integer sizes. - For a one-dimensional repeat of a scalar value, ARRAY_REPEAT(value, count) is a more terse alternative.
| Name | Type | Description |
|---|---|---|
value | Specifies the value used to fill every position in the resulting array. Accepts any scalar type; NULL is allowed and produces an all-NULL array. | |
dims | Specifies the size of each dimension as an integer array. ARRAY[3] creates a 3-element flat array; ARRAY[2, 3] creates a two-dimensional 2-by-3 array. |
-- Fill a 1D array with zeros
SELECT ARRAY_FILL(0, ARRAY[5]); -- [0, 0, 0, 0, 0]-- Fill with a string for a lookup bootstrap
SELECT ARRAY_FILL('pending', ARRAY[3]); -- ['pending', 'pending', 'pending']-- Fill with NULL
SELECT ARRAY_FILL(CAST(NULL AS INT), ARRAY[4]); -- [NULL, NULL, NULL, NULL]-- Two-dimensional fill
SELECT ARRAY_FILL(1, ARRAY[2, 3]); -- [[1,1,1],[1,1,1]]-- Pre-allocate a per-session zero vector for a downstream metric
SELECT session_id, ARRAY_FILL(0.0, ARRAY[24]) AS hourly_zero_vector
FROM analytics.events.user_sessions;-- Zero-sized dimension returns an empty array
SELECT ARRAY_FILL(1, ARRAY[0]); -- []