Standard BigQuery Unnest and JOIN question

Question

I have a basic BigQuery Unnesting Join question

I have a Session Events Table and a Setting Event table. The Setting Event Table has a nested column called "data".

Once I flatten setting_event.data (aka unnest), a column from it needs to join to a column from the Session Events table.

Is there a way to directly JOIN Session Events Table to the unnested Setting_event.data? I've tried doing:

SELECT .... FROM SESSION_EVENTS LEFT JOIN UNNEST([setting_event.data])

However, I'm receiving a setting_event not found.

How would I do this in Standard BQ SQL?

Answer 1

SELECT ....
FROM `project.dataset.SESSION_EVENTS`
LEFT JOIN (
  SELECT ....
  FROM `project.dataset.SETTING_EVENT`, UNNEST(data)
)
ON ... = ...

Answer 2

You can do:

SELECT <FIELDS> FROM `<session_events>` 
LEFT JOIN
(SELECT <FIELDS> FROM `<setting_events>`, UNNEST(data)
)
ON <JOIN_CONDITION>

Or:

SELECT <FIELDS> FROM `<session_events>` 
LEFT JOIN
(SELECT <FIELDS> FROM `<setting_events>` t, t.data d
)
ON <JOIN_CONDITION>

In the second option, you should refer the fields inside t.data as d. I hope it helps

Answer 3

This should do what you want:

SELECT ....
FROM SESSION_EVENTS sese LEFT JOIN 
     (SETTING_EVENT sete CROSS JOIN
      UNNEST(se.data) se_data
     ) 
     ON sese.? = se_data

This is a solution where you don't need to have a nested SELECT (and hence listing columns that you want).

If you were using inner joins, I would not use the parentheses, but instead change the order of the table references:

SELECT ....
FROM SETTING_EVENT sete CROSS JOIN
     UNNEST(se.data) se_data JOIN
     SESSION_EVENTS sese
     ON sese.? = se_data