Search for a command to run...
Series
Everything you need to work effectively with nested and repeated data in BigQuery — ARRAY, STRUCT, UNNEST, ARRAY_AGG, and related functions.
I've noticed that a new JSON function has been added (in Preview) in BigQuery SQL - JSON_FLATTEN(). It allows us to flatten JSON arrays and return a single flat ARRAY, no matter how many nested levels there are. So where is this actually useful?➡️ Ha...
A practical reminder that not all simple solutions scale
I’ve come across this SQL transformation multiple times, and it’s an interesting two-way problem. 1️⃣ From columns to rows (ARRAY as UNPIVOT):We start with separate timestamps for different lifecycle events. To analyze events dynamically, we reshape ...
Here's an interesting SQL problem, similar to one I had to solve the other day. It involves some of our favourite BigQuery ARRAYS, but also correlated subqueries. Say we have a table events that represents some events, together with the city and the ...
title: "The relationship between ARRAY_AGG and UNNEST" seoTitle: "BigQuery ARRAY_AGG vs UNNEST: Opposite Operations Explained" seoDescription: "ARRAY_AGG packs rows into an ARRAY and UNNEST unpacks an ARRAY back into rows — they are inverse operation...
Whenever you're UNNESing an ARRAY, you're getting a Cartesian product between the row and the array contents. If you were to unnest another array, you'll get another Cartesian product between the output of the previous unnest and the elements in the ...
