CODEWITHSUNDEEP
sqlgoogle-bigquery
samuelbrody1249
samuelbrody1249

Reputation: 4767

Example of table function

Is the UNNEST an example of a table-function? It seems to produce a single named column if I'm understanding it correctly. Something like:

`vals`
[1,2,3]

unnest(vals) as v
`v`
1
2
3

with Table as (
  select [1,2,3] vals
 ) select v from Table, UNNEST(vals) as v

Is this an example of a table-function? If not, what kind of function is it? Are there any other predefined table functions in BQ?

Upvotes: 0

Views: 221

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

The 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.

So, you might may call it table function if you wish :o)

You can read more about UNNEST here

It seems to produce a single named column if I'm understanding it correctly

Not exactly correct. See example below

with Table as (
  select [struct(1 as a,2 as b),struct(3, 4), struct(5, 6)] vals
) 
select v.* from Table, UNNEST(vals) as v

with output

enter image description here

Upvotes: 1

Related Questions