Arrays & Structs Functions

Functions for working with arrays and structured data.

Creating Arrays

Function	Description
`ARRAY[...]`	Create from literals
`MAKE_ARRAY(...)`	Create from expressions
`ARRAY_AGG(expr)`	Aggregate values into array
`STRING_TO_ARRAY(str, delim)`	Split string into array

SELECT
  ARRAY[1, 2, 3] as numbers,
  MAKE_ARRAY(a, b, c) as from_columns,
  STRING_TO_ARRAY('a,b,c', ',') as from_string
FROM data

ARRAY_AGG

Collect values from multiple rows:

SELECT
  user_id,
  TUMBLE(interval '1 hour') as window,
  ARRAY_AGG(product_id) as products_viewed,
  ARRAY_AGG(product_id ORDER BY timestamp) as products_in_order,
  ARRAY_AGG(DISTINCT category) as unique_categories
FROM page_views
GROUP BY user_id, window

Array Information

Function	Description	Example
`ARRAY_LENGTH(arr)`	Number of elements	`ARRAY_LENGTH(ARRAY[1,2,3])` → `3`
`CARDINALITY(arr)`	Total elements (alias)	Same as above
`ARRAY_EMPTY(arr)`	Check if empty	`ARRAY_EMPTY(ARRAY[])` → `true`

SELECT * FROM orders
WHERE ARRAY_LENGTH(items) > 0

Accessing Elements

Arrays are 1-indexed:

SELECT
  tags[1] as first_tag,
  tags[2] as second_tag,
  tags[ARRAY_LENGTH(tags)] as last_tag
FROM posts

Searching Arrays

Function	Description
`ARRAY_CONTAINS(arr, val)`	Check if value exists
`ARRAY_HAS(arr, val)`	Alias for contains
`ARRAY_POSITION(arr, val)`	Find index of value
`ARRAY_POSITIONS(arr, val)`	Find all indices

SELECT * FROM users
WHERE ARRAY_CONTAINS(roles, 'admin')
 
SELECT
  product_id,
  ARRAY_POSITION(tags, 'featured') as featured_position
FROM products

Modifying Arrays

Adding Elements

Function	Description
`ARRAY_APPEND(arr, val)`	Add to end
`ARRAY_PREPEND(val, arr)`	Add to beginning
`ARRAY_CONCAT(arr1, arr2)`	Combine arrays

SELECT
  ARRAY_APPEND(tags, 'new') as with_new_tag,
  ARRAY_CONCAT(tags, extra_tags) as all_tags
FROM posts

Removing Elements

Function	Description
`ARRAY_REMOVE(arr, val)`	Remove first occurrence
`ARRAY_REMOVE_N(arr, val, n)`	Remove first n occurrences
`ARRAY_REMOVE_ALL(arr, val)`	Remove all occurrences

SELECT
  ARRAY_REMOVE(tags, 'deprecated') as cleaned_tags,
  ARRAY_REMOVE_ALL(values, 0) as no_zeros
FROM data

Replacing Elements

Function	Description
`ARRAY_REPLACE(arr, from, to)`	Replace first occurrence
`ARRAY_REPLACE_ALL(arr, from, to)`	Replace all occurrences

SELECT
  ARRAY_REPLACE_ALL(status_codes, 'pending', 'processing') as updated
FROM batches

Transforming Arrays

Function	Description
`ARRAY_SORT(arr)`	Sort elements
`ARRAY_REVERSE(arr)`	Reverse order
`ARRAY_DISTINCT(arr)`	Remove duplicates
`ARRAY_SLICE(arr, start, end)`	Extract portion
`FLATTEN(arr)`	Flatten nested arrays

SELECT
  ARRAY_SORT(scores) as sorted_scores,
  ARRAY_DISTINCT(categories) as unique_categories,
  ARRAY_SLICE(items, 1, 5) as first_five,
  FLATTEN(ARRAY[ARRAY[1,2], ARRAY[3,4]]) as flat  -- [1,2,3,4]
FROM data

Set Operations

Function	Description
`ARRAY_INTERSECT(a, b)`	Common elements
`ARRAY_UNION(a, b)`	Combined unique elements
`ARRAY_EXCEPT(a, b)`	Elements in a but not b

SELECT
  ARRAY_INTERSECT(user_tags, required_tags) as matching_tags,
  ARRAY_UNION(tags_v1, tags_v2) as all_tags,
  ARRAY_EXCEPT(all_features, enabled_features) as disabled
FROM data

Converting Arrays

Function	Description
`ARRAY_TO_STRING(arr, delim)`	Join into string
`STRING_TO_ARRAY(str, delim)`	Split from string

SELECT
  ARRAY_TO_STRING(tags, ', ') as tags_string,
  STRING_TO_ARRAY(csv_values, ',') as values_array
FROM data

UNNEST

Expand an array into rows:

-- Input: {user_id: 1, tags: ['a', 'b', 'c']}
-- Output: 3 rows
 
SELECT user_id, UNNEST(tags) as tag
FROM users

Useful for:

Processing each array element
Filtering by array contents
Joining on array values

-- Find all users with a specific tag
SELECT DISTINCT user_id
FROM users, UNNEST(tags) as tag
WHERE tag = 'premium'

Structs

Accessing Struct Fields

Use dot notation:

SELECT
  address.street,
  address.city,
  address.zip,
  profile.settings.theme
FROM users

Creating Structs

SELECT STRUCT(
  first_name as first,
  last_name as last,
  email
) as contact
FROM users

Nested Structs

SELECT
  order_id,
  shipping.address.city as ship_city,
  billing.address.city as bill_city
FROM orders
WHERE shipping.address.country = 'US'

Practical Examples

Tag Analysis

SELECT
  UNNEST(tags) as tag,
  TUMBLE(interval '1 hour') as window,
  COUNT(*) as usage_count
FROM posts
GROUP BY tag, window
ORDER BY usage_count DESC

Find Common Elements

SELECT
  user_a,
  user_b,
  ARRAY_INTERSECT(a.interests, b.interests) as shared_interests,
  ARRAY_LENGTH(ARRAY_INTERSECT(a.interests, b.interests)) as match_score
FROM user_pairs
JOIN users a ON user_a = a.id
JOIN users b ON user_b = b.id

Aggregate into Arrays

SELECT
  customer_id,
  TUMBLE(interval '1 day') as window,
  ARRAY_AGG(order_id ORDER BY timestamp) as order_history,
  ARRAY_AGG(DISTINCT product_category) as categories_purchased
FROM orders
GROUP BY customer_id, window

Process Nested Data

SELECT
  event_id,
  payload.user.id as user_id,
  payload.user.properties.plan as plan,
  ARRAY_LENGTH(payload.items) as item_count
FROM events
WHERE ARRAY_CONTAINS(payload.user.roles, 'admin')