Unable to unnest in bigquery when there is comma inside list which is of type string

Question

Unable to unnest in bigquery when there is comma inside list which is of type string. I have data in following format where value is type of string

name value
Aashis ["A,B",AC"]
Rahul ["AA",AD"]

When I use following query, It is incorrectly splitting and unable to get logic right in regexp_replace

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Aashis' name, '["A,B","AC"]' value UNION ALL
  SELECT 'Rahul', '["AA","AD"]'
)
SELECT name, value
FROM `project.dataset.table`,
UNNEST(SPLIT(REGEXP_REPLACE(value, r'^\[|]$', ''))) value   

With the above code, I am getting

Aashis A
Aashis B
Aashis AC
Rahul AA
Rahul AD

My expected output is

Aashis AB
Aashis AC
Rahul AA
Rahul AD

I have followed following link converting array of strings to single row values

Any help is really appreciated

Answer 1

Try this:

WITH `project.dataset.table` AS (
  SELECT 'Aashis' name, '["A,B","AC"]' value UNION ALL
  SELECT 'Rahul', '["AA","AD"]'
)
SELECT name, value
FROM `project.dataset.table`,
UNNEST(SPLIT(TRIM(REGEXP_REPLACE(value, r'^\[|]$', ''), '"'),'","')) value