Below is for BigQuery Standard SQL

#standardSQL
SELECT * EXCEPT(status, locale, pos1, pos2), status, locale  
FROM `project.dataset.table`,
UNNEST(status) status WITH OFFSET pos1,
UNNEST(locale) locale WITH OFFSET pos2
WHERE pos1 = pos2

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.

Below is for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.MY_DB` AS (
  SELECT 1 id, '11-12-2000' startDate, '11-12-2010' endDate, 'Blue' info1, ['Circle','Triangle', 'Square'] info2, ['A', 'B'] info3 UNION ALL
  SELECT 2, '11-12-2001', '11-12-2011', 'Yellow', ['<*>'], ['C'] UNION ALL  
  SELECT 3, '11-12-2007', '11-12-2008', 'Brown', ['Circle','Triangle'], ['D', 'B'] 
)
SELECT id, startDate, endDate, info1, info2, info3
FROM `project.dataset.MY_DB`, UNNEST(info2) info2, UNNEST(info3) info3
WHERE info2 != '<*>' AND info3 = 'B'
ORDER BY id

with result as below

Row id  startDate   endDate     info1   info2       info3    
1   1   11-12-2000  11-12-2010  Blue    Circle      B    
2   1   11-12-2000  11-12-2010  Blue    Triangle    B    
3   1   11-12-2000  11-12-2010  Blue    Square      B    
4   3   11-12-2007  11-12-2008  Brown   Circle      B    
5   3   11-12-2007  11-12-2008  Brown   Triangle    B