What are the semantics of SQL 'FROM UNNEST'?

When reading an expression in a formal language, I'm used to reading from the inside out, i.e. understanding subexpressions and building up to the whole. In this SQL snippet:

SELECT (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'TITLE') AS level_id, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'url') AS url FROM `events_20180725` WHERE event_name = 'SCI_ERROR' 

One subexpression is

SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'TITLE' 

That's not a normal subquery: if I were to try to run it on its own, I would get an error because event_params isn't an array. So it seems that

• UNNEST can be used with something other than arrays.
• There is some kind of binding of the table events_20180725 used in the outer FROM which makes it accessible to the UNNEST inside the subquery.

contains some examples (under 'Querying Nested Arrays'), but doesn't actually explain the semantics. What's going on here?

2 Answers

UNNEST operator takes an ARRAY and returns a table, with one row for each element in the ARRAY. You can also use UNNEST outside of the FROM clause with the IN operator.

For input ARRAYs of most element types, the output of UNNEST generally has one column. This single column has an optional alias, which you can use to refer to the column elsewhere in the query. ARRAYS with these element types return multiple columns:

STRUCT UNNEST destroys the order of elements in the input ARRAY. Use the optional WITH OFFSET clause to return a second column with the array element indexes (see below).

For an input ARRAY of STRUCTs, UNNEST returns a row for each STRUCT, with a separate column for each field in the STRUCT. The alias for each column is the name of the corresponding STRUCT field.

You can read much more about UNNESTin a much more applicable section - FROM clause - go there and scroll a little down till UNNEST section

The outer query that selects from events_20180725 introduces the event_params into the scope of the select list. When you put a scalar subquery in the select list, that subquery can reference columns from the outer scope. The UNNEST function returns a relation given a column reference, which introduces other columns into the scope of the subquery, namely key and value in this case. In the case of this scalar subquery:

(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'TITLE') 

Filtering on key = 'TITLE' restricts the rows returned by UNNEST just to the one where the key has that value.