Use below
SELECT
col_a, col_b, el
FROM
`table`,
UNNEST(array) as el
Videos
That's because the comma is a cross join - in combination with an unnested array it is a lateral cross join. You repeat the parent row for every row in the array.
One problem with pivoting arrays is that arrays can have a variable amount of rows, but a table must have a fixed amount of columns.
So you need a way to decide for a certain row that becomes a certain column.
E.g. with
SELECT
id,
name,
groups[ordinal(1)] as firstArrayEntry,
groups[ordinal(2)] as secondArrayEntry,
keyword
FROM `project_id.dataset_id.table_id`
unnest(groups)
where id = 204358
If your array had a key-value pair you could decide using the key. E.g.
SELECT
id,
name,
(select value from unnest(groups) where key='key1') as key1,
keyword
FROM `project_id.dataset_id.table_id`
unnest(groups)
where id = 204358
But that doesn't seem to be the case with your table ...
A third option could be PIVOT in combination with your cross-join solution but this one has restrictions too: and I'm not sure how computation-heavy this is.
Consider below simple solution
select * from (
select id, name, keyword, offset
from `project_id.dataset_id.table_id`,
unnest(`groups`) with offset
) pivot (max(name) name for offset + 1 in (1, 2))
if applied to sample data in your question - output is
Note , when you apply to your real case - you just need to know how many such name_NNN columns to expect and extend respectively list - for example for offset + 1 in (1, 2, 3, 4, 5)) if you expect 5 such columns
In case if for whatever reason you want improve this - use below where everything is built dynamically for you so you don't need to know in advance how many columns it will be in the output
execute immediate (select '''
select * from (
select id, name, keyword, offset
from `project_id.dataset_id.table_id`,
unnest(`groups`) with offset
) pivot (max(name) name for offset + 1 in (''' || string_agg('' || pos, ', ') || '''))
'''
from (select pos from (
select max(array_length(`groups`)) cnt
from `project_id.dataset_id.table_id`
), unnest(generate_array(1, cnt)) pos
))
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.
