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
Advertisement
Answer
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