I have records in Big Query table as
name value
Aashis ["AB",AC"]
Rahul ["AA",AD"]
Here name and value column is String Type
I want the output as
name value
Aashis AB
Aashis AC
Rahul AA
Rahul AD
Add a comment
|
1 Answer
Below is for BigQuery Standard SQL
#standardSQL
SELECT name, value
FROM `project.dataset.table`,
UNNEST(value) value
You can test, play with above using dummy data from your question as below
#standardSQL
WITH `project.dataset.table` AS (
SELECT 'Aashis' name, ["AB","AC"] value UNION ALL
SELECT 'Rahul', ["AA","AD"]
)
SELECT name, value
FROM `project.dataset.table`,
UNNEST(value) value
with result
Row name value
1 Aashis AB
2 Aashis AC
3 Rahul AA
4 Rahul AD
Update for: the column value is of Type String and Unnest accepts array.How do I convert this column to array ?
See below example
#standardSQL
WITH `project.dataset.table` AS (
SELECT 'Aashis' name, '["AB","AC"]' value UNION ALL
SELECT 'Rahul', '["AA","AD"]'
)
SELECT name, value
FROM `project.dataset.table`,
UNNEST(SPLIT(REGEXP_REPLACE(value, r'^\[|]$', ''))) value
with result
Row name value
1 Aashis "AB"
2 Aashis "AC"
3 Rahul "AA"
4 Rahul "AD"
2 Comments
Aashis Aashu
Thanks Mikhail for the answer. Here the column value is of Type String and Unnest accepts array.How do I convert this column to array ?
Mikhail Berlyant
you clearly stated in your question that you have an
array of string. so looks like this is not a case! i will update the answer shortly