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.
Earlier in 2022 BigQuery introduced native support for the JSON datatype. Previously, one would have had to store the JSON data in a string column. This new development opens the door to a lot of interesting use cases, given the widespread adoption a...
Here's a common confusion that I encounter while working with ARRAYs in BigQuery. This can lead to wrong queries in some situations. Consider the following two snippets: FROM table, UNNEST(repeated_column) AS single_item versus FROM table LEFT JOIN...
Almost everybody knows the usual standard SQL aggregation functions like SUM, MAX or AVG. In this short post, we're going to look at ARRAY_AGG: another useful aggregation function. So, what does it do? ARRAY_AGG allows us to aggregate multiple rows i...
title: "Using STRUCTS for Audit Fields in BigQuery" seoTitle: "BigQuery STRUCT Columns for Data Pipeline Audit Fields" seoDescription: "Shows how to use nested STRUCT columns in BigQuery to store audit metadata without cluttering your schema. Practic...
Ever heard about value tables in BigQuery? Well, neither have I, until I've seen them mentioned in the docs. So, while in a normal table, a row is made up of columns, in a value table the row is a STRUCT. Say you UNNEST order_lines AS order_line. The...
If you're working with ARRAYS in BigQuery, you might need to combine two arrays into one at some point in time. That's why I want to showcase an interesting function - ARRAY_CONCAT_AGG. What does it do? It's an aggregation function, it allows us to s...
