Array & Map Functions

SELECT ARRAY('a', 'b', 'c') -- ['a', 'b', 'c']
SELECT ARRAY(1, 2, 3) AS numbers

SELECT SIZE(tags) AS tag_count FROM company_data."123"

-- 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"

SELECT ARRAY_FLATTEN(ARRAY(ARRAY(1, 2), ARRAY(3, 4))) -- [1, 2, 3, 4]

SELECT ARRAY_SORT(ARRAY(3, 1, 2)) -- [1, 2, 3]

-- 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

-- 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"

-- 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"

SELECT t.user_id, tag
FROM company_data."123" t
LATERAL JOIN EXPLODE(t.tags) AS tag

SELECT t.user_id, element
FROM company_data."123" t, UNNEST(t.tags) AS element