GET

Return the array element at a given 0-based index.

Syntax

GET(array, index)

Description

## Overview Returns the element at a 0-based index in the array. GET is the companion to ELEMENT_AT for code that prefers 0-based addressing (for example, ports of JVM or Scala code that carries over zero-based conventions), and it is a safe accessor because out-of-range or negative indices return NULL rather than raising. Typical uses include reading a known position in an array when the surrounding code uses 0-based arithmetic, and implementing quick accessors that match the semantics of common programming languages without translating to 1-based indexes. ## Behavior - Returns the element at position (index + 1) under this engine's 1-based storage: GET(arr, 0) is the first element, GET(arr, ARRAY_SIZE(arr) - 1) is the last. - Returns NULL for negative indices, for indices greater than or equal to ARRAY_SIZE, and for NULL inputs. - Element type matches the array's element type. - Runs in O(1). - Does not mutate the input. ## Null and empty handling - NULL input array returns NULL. - NULL index argument returns NULL. - Index < 0 or index >= ARRAY_SIZE returns NULL. - Empty array returns NULL for any index. - NULL at the referenced position returns NULL (indistinguishable from out-of-bounds). ## Compatibility - GET and ELEMENT_AT differ only in their index base: GET is 0-based, ELEMENT_AT is 1-based and supports negative indices. Choose whichever matches surrounding code. - Both functions are safe: they return NULL on missing positions rather than raising.

Parameters

Name	Type	Description
array		Specifies the input array.
index		Specifies the 0-based index. The first element is at index 0; negative indices and out-of-bounds values return NULL.

Examples

-- First element at index 0
SELECT GET(ARRAY[10, 20, 30], 0);  -- 10

-- Third element at index 2
SELECT GET(ARRAY['a', 'b', 'c'], 2);  -- 'c'

-- Out-of-bounds index returns NULL
SELECT GET(ARRAY[1, 2, 3], 5);  -- NULL

-- Negative index returns NULL (unlike ELEMENT_AT)
SELECT GET(ARRAY[1, 2, 3], -1);  -- NULL

-- Access the zeroth event in a per-session trail
SELECT session_id, GET(events, 0) AS first_event
FROM analytics.events.user_sessions;

-- NULL input array returns NULL
SELECT GET(CAST(NULL AS ARRAY<INT>), 0);  -- NULL

Pitfalls

• GET uses 0-based indexing while ELEMENT_AT uses 1-based indexing. Mixing the two in the same project is a frequent source of off-by-one bugs.
• GET returns NULL for negative indices. To address from the end, use ELEMENT_AT(arr, -k) or SLICE with computed bounds.
• Out-of-bounds GET returns NULL silently. Validate index against ARRAY_SIZE when bounds checking is required.
• A NULL result may mean "index out of range" or "element itself is NULL". Pair with ARRAY_SIZE to disambiguate.

See Also

• element_at
• array_first
• array_last
• array_slice