ARRAY_SLICE

Return the sub-array between two 1-based positions, inclusive on both ends.

Category: collectionReturns: ARRAY<T>Dialect: PostgreSql

Syntax

ARRAY_SLICE(array, start, end)

Description

## Overview Returns a contiguous subsection of the input array bounded by inclusive 1-based start and end positions. ARRAY_SLICE is the most common primitive for extracting the prefix, suffix, or middle of an array column without unnesting. Typical uses include extracting the first N events from a per-session trail, producing a fixed-length prefix or suffix for downstream vectorised code, and implementing paged or windowed views over a nested collection. ## Behavior - Returns an ARRAY<T> containing the elements from position start to position end, inclusive. - Indexing is 1-based. Position 1 is the first element; position ARRAY_SIZE(array) is the last. - Out-of-range bounds are silently clipped to the valid range. If start > end after clipping, the result is an empty array. - Order within the slice is preserved from the input. - NULL elements inside the slice are retained. - Never mutates the input. ## Null and empty handling - NULL input array returns NULL. - NULL start or end arguments return NULL. - Empty input array returns an empty array regardless of the bounds. - start > end after clipping returns an empty array (not NULL). - Bounds outside the array range are clipped; they do not raise an error. ## Compatibility - Matches the array/map SQL ARRAY_SLICE convention with inclusive 1-based endpoints. The SLICE(array, start, length) variant is also supported and takes a length instead of an end position. - Equivalent to array[start:end] syntax in dialects that support range subscripts.

Parameters

Name	Type	Description
`array`		Specifies the input array.
`start`		Specifies the 1-based starting position of the slice (inclusive).
`end`		Specifies the 1-based ending position of the slice (inclusive).

Examples

-- Middle slice
SELECT ARRAY_SLICE(ARRAY[10, 20, 30, 40, 50], 2, 4);  -- [20, 30, 40]

-- First two elements
SELECT ARRAY_SLICE(ARRAY['a', 'b', 'c', 'd'], 1, 2);  -- ['a', 'b']

-- Single-element slice
SELECT ARRAY_SLICE(ARRAY[1, 2, 3], 2, 2);  -- [2]

-- NULL elements inside the slice are preserved
SELECT ARRAY_SLICE(ARRAY[1, CAST(NULL AS INT), 3, 4], 1, 3);  -- [1, NULL, 3]

-- Extract the first five events from a per-session trail
SELECT session_id, ARRAY_SLICE(events, 1, 5) AS first_five
FROM analytics.events.user_sessions;

-- Out-of-range ends are clipped to the array length
SELECT ARRAY_SLICE(ARRAY[1, 2, 3], 2, 99);  -- [2, 3]

Pitfalls

Both endpoints are inclusive and 1-based. Do not confuse ARRAY_SLICE(array, start, end) with SLICE(array, start, length); the second argument of SLICE is a length.
Out-of-range bounds are clipped rather than raising. Code that needs strict validation must test bounds against ARRAY_SIZE.
start > end after clipping returns an empty array, not NULL. Use IS NULL tests only for NULL inputs; use ARRAY_SIZE for emptiness.
Negative indices are not supported. To take the last N elements, compute start = GREATEST(1, ARRAY_SIZE(arr) - n + 1) and end = ARRAY_SIZE(arr).
Slice order is preserved from the source. If ordering was non-deterministic, the slice is also non-deterministic; apply ARRAY_SORT upstream when determinism matters.