Use 2 keys in a query

Question

I have in where event_params.key = 'page_referrer' but, i want to see in select values event_params.key = 'traffic_type' I need both fields

I wanted to use JOIN tables with itself, but didn't work out

Answer 1

Would you try below ?

SELECT (SELECT value.string_value FROM t.event_params WHERE key = 'traffic_type') traffic_type
  FROM `coolclever-1148.analytic_xxxxxx.events_20230130` t
 WHERE event_name = 'view_item' AND 'page_referrer' IN UNNEST(event_params.key);

Example Query

To show you working example with public dataset:

SELECT (SELECT value.string_value FROM t.event_params WHERE key = 'page_location') page_location
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` t
 WHERE event_name = 'page_view' AND 'page_title' IN UNNEST(event_params.key);

Query results