Skip to content
Advertisement

Select multiple keys and values from a NESTED column

I’m linking Google Analytics 4 events into a BigQuery table. I can retrieve data just based on one key, but how do I get the value stored in another key in the same record as that key?

In specific, I want to rank the number of views by the name of the article, and then provide the author’s name in a separate column as a supplementary data (the author’s name is stored in the same record with a different key in a nested column).

Environment

Google Analytics 4 Set up events in Google Tag Manager

The table schema looks like this, with keys such as article_name author_name in event_params.key and the value you want to get in event_params.value.string_value.

enter image description here

And the table preview looks like this:

What I tried

Was able to pull off the article ranking itself, without the author name.

Result:

I wanted to add a column for author_name next to article_name here, so I thought it would be a good idea to use CASE WHEN. But as it turns out, the author_name will all be null, which probably means it is treated as a separate record.

Result:

When I GROUP BY with author_name in a descending order, the author’s name appears correctly, but this time the article_name is all null. Is it possible to have both article_name and author_name in the same record, and have the author name next to article name in the same ranking result?

Advertisement

Answer

I think you’re looking for a sub-select solution:

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement