SLICE

Return a sub-array starting at a position with a given length.

Category: collectionReturns: ARRAY<T>Dialect: Standard

Syntax

SLICE(array, start, length)

Description

## Overview Returns a sub-array of the input starting at a 1-based position and continuing for up to length elements. SLICE is the length-oriented slicing primitive and pairs naturally with ARRAY_SLICE (which uses endpoint-oriented indexing) to cover the common windowing patterns. Typical uses include extracting a prefix or suffix of known length from an array column, taking the last N elements using negative start, and implementing paged windows over per-row collections. ## Behavior - Returns an ARRAY<T> containing up to length elements starting at position start. - start is 1-based when positive. Negative values count from the end (-1 is the last element). - If start points past the end of the array or before the beginning, the result is clipped and may be empty. - If length extends beyond the available elements, only the available elements are returned. - 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 length returns NULL. - Negative length raises an error. - Zero length returns an empty array. - Empty input array returns an empty array for any start and length. - Start past the end of the array returns an empty array; it is not an error. ## Compatibility - Matches the array/map SQL convention for SLICE with a position and a length (as opposed to ARRAY_SLICE, which takes two endpoints). - Code that uses both functions in the same project should agree on a single semantic (position+length vs. endpoints) to avoid confusion.

Parameters

Name	Type	Description
`array`		Specifies the input array.
`start`		Specifies the 1-based starting position. Negative values count from the end of the array (-1 is the last element).
`length`		Specifies the number of elements to include. Must be a non-negative integer; zero produces an empty array.

Examples

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

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

-- Negative start counts from the end
SELECT SLICE(ARRAY[1, 2, 3, 4, 5], -3, 2);  -- [3, 4]

-- Length greater than remainder is clipped
SELECT SLICE(ARRAY[1, 2, 3], 2, 10);  -- [2, 3]

-- Zero length yields an empty array
SELECT SLICE(ARRAY[1, 2, 3], 1, 0);  -- []

-- Take the last three events of a per-session trail
SELECT session_id, SLICE(events, -3, 3) AS last_three
FROM analytics.events.user_sessions;

Pitfalls

SLICE and ARRAY_SLICE are not the same: SLICE takes (start, length), ARRAY_SLICE takes (start, end). Swapping these arguments silently produces a different result.
Negative start counts from the end, which differs from many bracket-indexing conventions. Document the intended semantics when mixing with ARRAY_FIRST or ELEMENT_AT.
Negative length raises; validate the length upstream when it is computed from untrusted data.
Out-of-range start clips to empty without raising. Use ARRAY_SIZE for strict bounds checking.
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.