ARRAY_JOIN

Concatenate array elements into a single delimited string.

Category: collectionReturns: STRINGDialect: Standard

Syntax

ARRAY_JOIN(array, delimiter [, null_replacement])

Description

## Overview Concatenates all elements of the array into a single string, placing the given delimiter between consecutive values. ARRAY_JOIN is the array-aware counterpart of the scalar CONCAT_WS and the SQL equivalent of join/implode in procedural languages, and it is the preferred way to produce a human-readable or machine-parseable serialisation of array data without an explicit EXPLODE-and-LISTAGG rewrite. Typical uses include rendering tag lists for display, flattening a per-row array column into a CSV cell for export, and composing cache keys or stable identifiers from arrays of attributes. ## Behavior - Returns a STRING formed by the string representation of each element concatenated with the delimiter between consecutive items. - Non-string element types are cast to STRING using the engine's standard conversion rules. For floating-point types the default format is used; call FORMAT_NUMBER for deterministic presentation. - When null_replacement is omitted, NULL elements are skipped and the delimiter is placed only between retained elements. - When null_replacement is provided, NULL elements are replaced with the literal replacement string and joined like any other element. - An empty array produces an empty string; a single-element array produces just that element's string form. - The delimiter itself can be any string, including an empty string for seamless concatenation. ## Null and empty handling - NULL input array returns NULL. - NULL delimiter returns NULL. - Empty input array returns an empty string (not NULL). - NULL elements: omitted when null_replacement is not supplied; replaced with null_replacement when it is. - If null_replacement itself is NULL, NULL elements revert to being skipped. ## Compatibility - Matches the array/map SQL convention for ARRAY_JOIN. Equivalent to LISTAGG over a pre-unnested column, but runs in a single expression without set-valued output.

Parameters

Name	Type	Description
`array`		Specifies the input array. Elements are cast to STRING before concatenation.
`delimiter`		Specifies the separator placed between consecutive non-skipped elements.
`null_replacement`		Specifies an optional replacement for NULL elements. When omitted, NULL elements are skipped; when provided, NULL elements are replaced with this string and joined like any other element.

Examples

-- Basic join with a comma
SELECT ARRAY_JOIN(ARRAY['a', 'b', 'c'], ', ');  -- 'a, b, c'

-- Join numbers with a dash
SELECT ARRAY_JOIN(ARRAY[1, 2, 3], '-');  -- '1-2-3'

-- NULL elements skipped by default
SELECT ARRAY_JOIN(ARRAY['a', CAST(NULL AS STRING), 'c'], ', ');  -- 'a, c'

-- NULL replacement included
SELECT ARRAY_JOIN(ARRAY['a', CAST(NULL AS STRING), 'c'], ', ', 'N/A');  -- 'a, N/A, c'

-- Render per-session tag arrays for a report
SELECT session_id, ARRAY_JOIN(tags, ', ', '(none)') AS tag_list
FROM analytics.events.user_sessions;

-- Empty array produces an empty string
SELECT ARRAY_JOIN(CAST(ARRAY() AS ARRAY<STRING>), ', ');  -- ''

Pitfalls

By default NULL elements are skipped rather than rendered. Supply a null_replacement such as '' or '(null)' when callers must see every slot.
The delimiter is treated as a raw string, not a regex. Multi-character delimiters are literal.
Numeric element formatting uses the engine default; for consistent localisation or precision wrap elements with FORMAT_NUMBER or CAST(x AS DECIMAL(m,n)).
ARRAY_JOIN on very large arrays allocates a proportional string buffer. Chunk the source with SLICE when output size needs to be bounded.