ARRAY_REPLACE

Return a new array with every occurrence of a value replaced by another value.

Category: collectionReturns: ARRAY<T>Dialect: PostgreSql

Syntax

ARRAY_REPLACE(array, from, to)

Description

## Overview Returns a new array in which every element equal to the from value is replaced with the to value. Length, ordering, and element positions are preserved. ARRAY_REPLACE is the most direct way to perform a value-level rewrite on an array column without unnesting or using a per-element TRANSFORM. Typical uses include normalising legacy or deprecated values in a tag list, censoring sensitive tokens with NULL or a placeholder, and retargeting sentinel values to their current canonical form. ## Behavior - Produces an ARRAY<T> with the same length as the input. - Element positions of non-matching values are preserved exactly. - Equality is structural and uses SQL comparison rules. - NULL handling is explicit: from = NULL matches NULL elements; to = NULL writes NULL into matching positions. - Both arguments must be type-compatible with the array's element type. Numeric widening follows standard promotion rules. - Never mutates the input. ## Null and empty handling - NULL input array returns NULL. - Empty input array returns an empty array of the same element type. - from = NULL matches NULL elements and replaces them with to. - to = NULL writes NULL into the matched positions without changing array length. - If both from and to are NULL, the array is returned unchanged (structurally equivalent). ## Compatibility - Matches the standard-compatible ARRAY_REPLACE convention. Other array/map SQL dialects expose equivalent rewrite primitives; semantics align on NULL handling. - Equivalent to TRANSFORM(array, x -> CASE WHEN x IS NOT DISTINCT FROM from THEN to ELSE x END), without requiring a lambda.

Parameters

Name	Type	Description
`array`		Specifies the input array. The result is a new array; the input is never modified.
`from`		Specifies the value to replace. Every element equal to this value is substituted with to. Pass NULL to target NULL elements.
`to`		Specifies the replacement value. Must be type-compatible with the array's element type. NULL is allowed.

Examples

-- Replace all occurrences
SELECT ARRAY_REPLACE(ARRAY[1, 2, 1, 3], 1, 99);  -- [99, 2, 99, 3]

-- Replace with NULL (censoring)
SELECT ARRAY_REPLACE(ARRAY['a', 'b', 'c'], 'b', CAST(NULL AS STRING));  -- ['a', NULL, 'c']

-- Replace NULL with a default
SELECT ARRAY_REPLACE(ARRAY[1, CAST(NULL AS INT), 3], CAST(NULL AS INT), 0);  -- [1, 0, 3]

-- No match leaves the array unchanged
SELECT ARRAY_REPLACE(ARRAY[1, 2, 3], 5, 99);  -- [1, 2, 3]

-- Normalise a sentinel tag in per-session arrays
SELECT session_id, ARRAY_REPLACE(tags, 'legacy_vip', 'vip') AS tags
FROM analytics.events.user_sessions;

-- Combine with ARRAY_AGG to rewrite group-level arrays in bulk
SELECT user_id, ARRAY_REPLACE(ARRAY_AGG(event_type), 'ping', 'heartbeat') AS trail
FROM analytics.events.user_sessions
GROUP BY user_id;

Pitfalls

ARRAY_REPLACE replaces every occurrence. To replace only the first, use ARRAY_POSITION to locate the index and then compose SLICE and ARRAY_INSERT.
Replacing with NULL leaves the array length unchanged. Use ARRAY_REMOVE if the intent is to drop the matched positions.
Equality is structural for complex element types. A STRUCT whose fields differ only in NULL positions is considered distinct and is not replaced.
The from and to arguments must widen to the array's element type. Mismatched types raise a compile-time error.