CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquery
Casper Bang
Casper Bang

Reputation: 569

BigQuery UNNEST including empty rows?

For BigQuery, we can extract the special repeated structs using UNNEST. This works very well when you have data in the struct key and value (one of string, int or double). However, occasionally I have a struct with a key ("foo") and often with data value.string_value but not always. The following will NOT include the row when there is no string_value:

SELECT
  event_timestamp,
  event_params_custom.value.string_value
FROM 
  `my_database.events_*` T,
  UNNEST(T.event_params) event_params_custom
WHERE
  event_name = "some_custom_event"
  AND event_params_custom.key = "foo"

Is it possible to do something akin to "outer join" and include all rows even when there is no value for the key "foo"?

Upvotes: 2

Views: 2732

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

#standardSQL
SELECT
  event_timestamp,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "foo") AS string_value
FROM 
  `my_database.events_*`
WHERE
  event_name = "some_custom_event"

Upvotes: 4

Related Questions