BigQuery: Converting key-value pairs in Array to columns

Question

I reference this post and would like to convert selected key-value pairs in the event_params field to columns. My table is something like this:

| user_id | event_params.key | event_params.value.string_value |
+---------+------------------+---------------------------------+
|  1      |               k1 |                             v11 |
|         +------------------+---------------------------------+
|         |               k2 |                             v12 |
|         +------------------+---------------------------------+
|         |               k3 |                             v13 |
+---------+------------------+---------------------------------+
|  1      |               k1 |                             v21 |
|         +------------------+---------------------------------+
|         |               k2 |                             v22 |
|         +------------------+---------------------------------+
|         |               k3 |                             v23 |
+---------+------------------+---------------------------------+
|  2      |               k1 |                             v31 |
|         +------------------+---------------------------------+
|         |               k2 |                             v32 |
|         +------------------+---------------------------------+
|         |               k3 |                             v33 |

Each big row has an arbitrary N number of rows inside the event_params field and each big row can be repeated as it can be generated by the same user. I would like to retain this repetition.

My desired end result:

| user_id |  k1 |  k3 |
+---------+-----+-----+
|       1 | v11 | v13 |
+---------+-----+-----+
|       1 | v21 | v23 |
+---------+-----+-----+
|       2 | v31 | v33 |

My query so far:

SELECT 
  user_id, 
  IF(event_params.key = 'k1', event_params.value.string_value, NULL) AS k1,
  IF(event_params.key = 'k3', event_params.value.string_value, NULL) AS k3,
FROM `my-proj-id.analytics_xxxxx.events_20201030`
  , UNNEST(event_params) AS event_params
WHERE event_name='my-event-name'

event_name is omitted from table above for brevity.

My current implementation resulted in NULL in the whole of column k3, which I figured that it could possibly be due to the UNNEST and no one unnested row would contain both k1 and k3 together. How do I convert to my desired end result above?

Note: I would like to write in standard SQL

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
select user_id,
  (select value.string_value from t.event_params where key = 'k1') as k1,
  (select value.string_value from t.event_params where key = 'k3') as k3  
from `my-proj-id.analytics_xxxxx.events_20201030` t

if to apply to sample data from your question - output is

Answer 2

You can do this in the FROM clause using LEFT JOINs:

SELECT e.user_id, 
       event_k1.value.string_value AS k1,
       event_k3.value.string_value AS k3
FROM `my-proj-id.analytics_xxxxx.events_20201030` e LEFT JOIN
     UNNEST(e.event_params) event_k1
     ON event_k1.key = 'k1' LEFT JOIN
     UNNEST(e.event_params) event_k3
     ON event_k3.key = 'k3'
WHERE e.event_name = 'my-event-name';