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
Videos
What is unnest in PostgreSQL?
How to unnest text in PostgreSQL?
What is the opposite of Unnest in PostgreSQL?
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.