Multiple unnest hits custom Dimensions BigQuery

Question

I have this table in BigQuery

+---------------------------+---------------------------+--------------------------+|
|         date              |hits.customDimensions.index|hits.customDimensions.value|
+---------------------------+---------------------------+---------------------------|
|      24/09/2021           |             1             |            ARG            |
|                           |             2             |         production        |
|                           |             3             |            id1            |
|                           |             4             |       label1|label2       |
|      24/09/2021           |             1             |            GER            |
|                           |             2             |         production        |
|                           |             3             |            id2            |
|                           |             4             |       label1|label4       |
+---------------------------+---------------------------+---------------------------+

I would like to get a table like this:

+-------------++----------+----------+
|     date    |  Country |  labels  |
+-------------+----------+----------+
|  24/09/2021 |    ARG   |  label1  |
|  24/09/2021 |    ARG   |  label2  |
|  24/09/2021 |    GER   |  label1  |
|  24/09/2021 |    GER   |  label4  |
+-------------+----------+----------+

I tried with UNNEST hits.customdimensions individually but I could't get join the information of country and labels in one table.

user_5221944 · Answer

Consider below approach

select date, 
  (
    select value from t.hits hit,
    hit.customDimensions customDimension
    where index = 1
  ) country,
  label
from data t,
unnest((
    select split(value, '|') from t.hits hit,
    hit.customDimensions customDimension
    where index = 4
  )) label

if applied to sample data in your question - output is

Multiple unnest hits custom Dimensions BigQuery

1 Answers