How to unnest BigQuery nested records into multiple columns

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 
))

Your question is a little unclear, because it does not specify what to do with other keywords or other columns. If you specifically want the first two values in the array for keyword "OVG", you can unnest the array and pull out the appropriate names:

SELECT id,
       (SELECT g.name
        FROM UNNEST(t.groups) g WITH OFFSET n
        WHERE key = 'OVG'
        ORDER BY n
        LIMIT 1
       ) as name_1,
       (SELECT g.name
        FROM UNNEST(t.groups) g WITH OFFSET n
        WHERE key = 'OVG'
        ORDER BY n
        LIMIT 1 OFFSET 1
       ) as name_2,
       'OVG' as keyword
FROM `project_id.dataset_id.table_id` t
WHERE id = 204358;