Complex Data: Advanced

Apply functions to every array element

TRANSFORM(array, lambda) applies a function to every element in an array, returning a new array with the transformed values. This is conceptually similar to map() in functional programming.

The lambda x -> x * 2 takes each element x and returns x multiplied by 2. The result is a new array with each original element doubled.

Lambda bodies can contain complex expressions including CASE statements, nested functions, and conditionals:

This applies a 10% discount to prices over 100. The CASE expression evaluates for each array element independently.

You can chain TRANSFORM with other array functions. For example, applying TRANSFORM first to normalize values, then ARRAY_JOIN to produce a display string.

Select and combine array values with logic

FILTER(array, lambda) returns a new array containing only elements for which the lambda returns true. This is equivalent to a WHERE clause but for array elements.

The lambda p -> p > 50 evaluates to true for prices greater than 50. Only those elements are included in the result array.

REDUCE(array, initial, input_function, output_function) aggregates array elements into a single value using an accumulator pattern.

Starting with 0, the lambda (s, x) -> s + x adds each element to the running sum. The output function s -> s returns the final accumulator unchanged.

FILTER is often the first step in a processing pipeline. By removing unwanted elements early, subsequent TRANSFORM or REDUCE operations work on a smaller, cleaner dataset.

Unlike WHERE clauses that remove entire rows, FILTER operates within a single cell. The row stays in the result; only the array contents are reduced.

Combine FILTER with CARDINALITY to count matching elements without materializing the filtered array in a separate column.

Turn one row with an array into many rows

UNNEST expands an array into individual rows, enabling you to apply standard SQL operations to array elements. This is the opposite of array aggregation.

The CROSS JOIN UNNEST pattern creates one row per array element. The original order_id repeats for each item, expanding 3 input rows into 6 output rows.

Once unnested, you can apply WHERE clauses to filter individual elements:

UNNEST enables aggregations across array elements from multiple rows:

Choosing between UNNEST and lambda functions depends on your use case:

UNNEST multiplies rows: an array with five elements produces five output rows. Keep this expansion in mind when estimating the size of the result set.

UNNEST is especially useful when you need to join array elements against another table, something that lambda functions cannot do without subqueries.

-- 10M users x 2,000 avg elements
-- 50+ features needed per user
-- Nightly batch, 4-hour SLA

Real pipelines require balancing lambda-based in-array processing against UNNEST-based row expansion. The right choice depends on downstream usage and data volume.

When arrays are small and transformations simple, lambdas keep the row structure intact and avoid the row explosion associated with UNNEST.

For complex aggregations or joins against external tables, UNNEST followed by GROUP BY is often the cleaner and more maintainable approach.

Reshape complex nested data structures

ARRAY_JOIN(array, delimiter) concatenates array elements into a single string with the specified delimiter. This is essential for displaying or exporting array data.

MAP_FROM_ENTRIES(array_of_pairs) constructs a map from an array of key-value pair structures. This enables dynamic map creation from computed values.

This chain: 1) FILTER removes spam tags, 2) TRANSFORM uppercases remaining tags, 3) ARRAY_JOIN formats the result as a string.

ARRAY_JOIN is particularly useful for report generation and CSV exports where array types are not supported by the consumer.

MAP_FROM_ENTRIES is the inverse of MAP_ENTRIES: one builds a map from key-value pairs, while the other deconstructs a map back into an array of pairs.

Chaining FILTER, TRANSFORM, and ARRAY_JOIN in a single expression keeps the data in array form until the final step, reducing intermediate row expansion.

Solve production problems with nested data

This pattern filters events to the last 7 days, counts them, and extracts the first event type. The WITH clause makes the logic readable and reusable.

The FILTER-then-TRANSFORM pattern reduces processing volume by removing unwanted elements before applying expensive transformations.

This in-array processing pattern avoids UNNEST row expansion while keeping the data in its original row structure.