Loading lesson...
TRANSFORM, FILTER, REDUCE: lambdas meet arrays
TRANSFORM, FILTER, REDUCE: lambdas meet arrays
Topics covered: TRANSFORM for Array Mapping, FILTER and REDUCE, UNNEST for Array Expansion, Advanced Map Operations, Real-World Patterns
Lambda Syntax Lambda functions in SQL use arrow notation: element -> expression. The element name is arbitrary and scoped to the lambda body. String Transformations Lambda expressions can apply string functions to every element: Each tag is converted to uppercase. This pattern is useful for normalizing user-generated content before analysis. Complex Expressions Lambda expressions like this are the foundation of in-array transformations. The original array remains unchanged, and the result is a n
Array manipulation goes beyond reading elements. You can filter, reduce, and aggregate array contents directly in SQL. FILTER for Conditional Rows FILTER with CARDINALITY A common pattern counts how many elements match a condition: This filters the reviews array to include only ratings of 4 or higher, then counts the result. The original reviews array is unchanged. REDUCE for Aggregation REDUCE has four components: the array, an initial accumulator value, a function that combines each element wi
Basic UNNEST Filtering Unnested Data This filters the unnested items to include only those containing "premium". The result shows only matching items with their order IDs. Aggregating Unnested Arrays After unnesting, standard aggregation functions work on the expanded rows. This counts total item occurrences and unique items across all orders. UNNEST vs Lambda Functions After unnesting, you can group by the original row identifier to recollect elements, or apply aggregations across all elements
Maps and arrays become most useful when you can convert them to display-friendly formats and build them from query results. ARRAY_JOIN: String Concat The tags array is converted to a comma-separated string. This is particularly useful for reports or CSV exports where array types are not supported. MAP_FROM_ENTRIES Each ROW represents a key-value pair. The resulting map can be queried using bracket notation like any other map. Combining Operations Real-world queries often chain multiple array and
These patterns combine multiple array operations to solve common analytics problems you will encounter in production. Sessionization with Arrays Event streams often arrive as arrays that need to be filtered and transformed: Recommendation Arrays Recommendation systems often store results as arrays that need scoring and filtering: This multiplies recommendation scores by user affinity, then filters to keep only high-confidence recommendations above 0.7. Performance Considerations Lambda functions