Subqueries in BigQuery SQL

A subquery is a query that appears inside another SQL query statement. Subqueries are also referred to as sub-SELECTs or nested SELECTS.

There are various types of subqueries and I will introduce them as required in future posts.

One of the most common subqueries that you will use for GA4 data in BigQuery is called Expression Suqquery.

Expression Subquery is used in SQL expressions and returns only a single value.

There are different types of Expression subqueries and again, I will also introduce them as required.

In this post, I will show you Scalar subquery, a type of Expression subquery that helps you use the event paramater values that are stored in the nested column.

To understand a subquery and scalar subquery let’s take a look at an example.

SELECT event_name, 
(SELECT value.string_value from unnest(event_params) WHERE key = 'page_location') as page_location
FROM `your-project.your-dataset.events_######`

This query has two values in the SELECT list:

1. event_name column from the table used in the FROM clause

2. a subquery that selects the value from the event_paramaters column after unnesting it. In the above example, we are selecting the value of the “page_location” column that’s associated with the event_name from the main query.

Note: The subquery should be enclosed in parenthesis, as in the above query.

The result of this query will be similar to what you see below.

We will be using this type of subquery in various examples in the future so make sure to understand this concept.

Advanced Notes & SQL

Subscribe to Premium Membership to read the rest.