Skip to main content

​
Array and map functions

​
ARRAY

Creates an array from values. 
SELECT ARRAY('a', 'b', 'c') -- ['a', 'b', 'c']
SELECT ARRAY(1, 2, 3) AS numbers

​
SIZE

Returns the number of elements in an array. 
SELECT SIZE(tags) AS tag_count FROM company_data."123"

​
ELEMENT_AT

Returns an element at a specific index (1-based) or map key. 
-- Array (1-based index)
SELECT ELEMENT_AT(ARRAY('a', 'b', 'c'), 2) -- 'b'

-- Map
SELECT ELEMENT_AT(properties, 'color') AS color FROM company_data."123"

​
ARRAY_FLATTEN

Flattens nested arrays into a single array. 
SELECT ARRAY_FLATTEN(ARRAY(ARRAY(1, 2), ARRAY(3, 4))) -- [1, 2, 3, 4]

​
ARRAY_SORT

Sorts array elements. 
SELECT ARRAY_SORT(ARRAY(3, 1, 2)) -- [1, 2, 3]

​
ARRAY_POSITION

Returns the 0-based index of the first occurrence of an element in an array. Returns NULL if the element is not found or if the search value is NULL. Syntax: ARRAY_POSITION(array, value) 
-- Find the position of an element
SELECT ARRAY_POSITION(ARRAY('john', 'jack', 'jill'), 'jack') -- 1

-- Use with a dataset column
SELECT ARRAY_POSITION(tags, 'premium') AS premium_index
FROM company_data."123"

-- Returns NULL when not found
SELECT ARRAY_POSITION(ARRAY('a', 'b', 'c'), 'z') -- NULL
ARRAY_POSITION uses 0-based indexing, so the first element is at position 0.

​
FILTER

Filters elements of an array using a lambda predicate. Returns a new array containing only elements for which the lambda returns true. Syntax: FILTER(array, element -> condition) 
-- Keep only elements matching a condition
SELECT FILTER(tags, t -> t = 'active') AS active_tags
FROM company_data."123"

-- Filter with IN operator
SELECT FILTER(names, n -> n IN ('john', 'jack')) AS matched_names
FROM company_data."123"

-- Combine FILTER with other array functions
SELECT SIZE(FILTER(tags, t -> t = 'premium')) AS premium_count
FROM company_data."123"

​
TRANSFORM

Applies a function to each element of an array, returning a new array of transformed values. Syntax: TRANSFORM(array, element -> expression) 
-- Uppercase every element
SELECT TRANSFORM(names, n -> UPPER(n)) AS upper_names
FROM company_data."123"

-- Extract a field from an array of structs
SELECT TRANSFORM(items, x -> x."name") AS item_names
FROM company_data."123"

-- Chain FILTER and TRANSFORM together
SELECT TRANSFORM(FILTER(names, x -> x IN ('john', 'jack')), n -> UPPER(n)) AS result
FROM company_data."123"

​
EXPLODE

Converts an array into multiple rows (use with LATERAL JOIN). 
SELECT t.user_id, tag
FROM company_data."123" t
LATERAL JOIN EXPLODE(t.tags) AS tag

​
UNNEST

Expands an array into rows (alternative to EXPLODE). 
SELECT t.user_id, element
FROM company_data."123" t, UNNEST(t.tags) AS element

​
Related content

String Functions

Text manipulation

Conditional Functions

IF, COALESCE, NULLIF

All Functions

Browse all function categories

Data Types

ARRAY, MAP, and STRUCT type reference