What you are describing is often referred to as a pivot table - a transformation where values are used as columns. SQL doesn't generally support this as SQL is designed around the concept of having a fixed schema while pivot table requires dynamic schemas.

However if you have a fixed set of index columns you can emulate it with something like:

SELECT
  name,
  age,
  ARRAY(SELECT value FROM UNNEST(customFields) WHERE index="1")[SAFE_OFFSET(0)] AS index_1,
  ARRAY(SELECT value FROM UNNEST(customFields) WHERE index="2")[SAFE_OFFSET(0)] AS index_2,
  ARRAY(SELECT value FROM UNNEST(customFields) WHERE index="3")[SAFE_OFFSET(0)] AS index_3
FROM your_table;

What this does is specifically define columns for each index that picks out the right values from the customFields array.

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.

Use below

SELECT
  col_a, col_b, el
FROM
  `table`,
UNNEST(array) as el

Yet another version - with "explicit" UNNEST involved

WITH x AS (SELECT ARRAY[1,3,2] AS arr)
SELECT arr_item FROM x, UNNEST(arr) as arr_item

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

Below is for BigQuery Standard SQL

#standardSQL
SELECT acname, amount, domain
FROM `project.dataset.dummy`,
UNNEST(SPLIT(domains)) domain  

You can test, play with above using dummy data from your question as in example below

#standardSQL
WITH `project.dataset.dummy` AS (
  SELECT 'abc' acname, 100 amount, 'a,b,c' domains UNION ALL
  SELECT 'pqr', 300, 'p,q,r' UNION ALL
  SELECT 'lmn', 500, 'l,m,n'
)
SELECT acname, amount, domain
FROM `project.dataset.dummy`,
UNNEST(SPLIT(domains)) domain   

with output

Row acname  amount  domain   
1   abc     100     a    
2   abc     100     b    
3   abc     100     c    
4   pqr     300     p    
5   pqr     300     q    
6   pqr     300     r    
7   lmn     500     l    
8   lmn     500     m    
9   lmn     500     n     

The source table project.dataset.dummy which had field "domains" has comma separated values but after the comma there is a space (e.g. 'a'commaspace'b'commaspacec a, b, c). This results in space before the values b c q r m n; in the field "domains" in "Output After Unnest" table. Now I'm joining this table with "salesdomain" as a key. But because of space before b c q r m n, the output received is not correct

To address this - you can just simply use TRIM function to removes all leading and trailing spaces, like in example below

#standardSQL
WITH `project.dataset.dummy` AS (
  SELECT 'abc' acname, 100 amount, 'a, b, c' domains UNION ALL
  SELECT 'pqr', 300, 'p, q, r' UNION ALL
  SELECT 'lmn', 500, 'l, m, n'
)
SELECT acname, amount, TRIM(domain, ' ') domain
FROM `project.dataset.dummy`,
UNNEST(SPLIT(domains)) domain