Subqueries in BigQuery SQL

Pulling values from GA4 Event Paramaters

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

Advanced notes are available for the paid members only.

I am spending a lot of hours researching and coming up with these posts and SQL code. I don’t want you to waste your time researching the same thing.

I highly encourage you to become a paying member for a very low price today and save a lot of hours, headaches, and money.

Subscribe to Premium Membership to read the rest.

Become a paying subscriber of Premium Membership to get access to this post and other subscriber-only content.

Already a paying subscriber? Sign In

A subscription gets you:
Advanced Content
Readymade SQL Scripts

Subscribe to keep reading

This content is free, but you must be subscribed to GA4 BigQuery For Marketing Analytics to continue reading.

Already a subscriber?Sign In.Not now

Join the conversation

or to participate.