将字符串数组转换为单行值 [英] converting array of strings to single row values

问题描述

我在Big Query表中有记录为
名称值
Aashis ["AB"，AC]
拉胡尔["AA"，AD]

I have records in Big Query table as
name value
Aashis ["AB",AC"]
Rahul ["AA",AD"]

名称和值"列为字符串类型"

Here name and value column is String Type

我希望输出为
名称值
Aashis AB
Aashis AC
拉胡尔AA
拉胡尔AD

I want the output as
name value
Aashis AB
Aashis AC
Rahul AA
Rahul AD

推荐答案

以下是BigQuery标准SQL

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

有结果

Row name    value    
1   Aashis  AB   
2   Aashis  AC   
3   Rahul   AA   
4   Rahul   AD     

更新为:列值为String类型，并且Unnest接受array.如何将该列转换为array?

Update for: the column value is of Type String and Unnest accepts array.How do I convert this column to array ?

请参见下面的示例

#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   

有结果

Row name    value    
1   Aashis  "AB"     
2   Aashis  "AC"     
3   Rahul   "AA"     
4   Rahul   "AD"     

这篇关于将字符串数组转换为单行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！