google-bigquery - Bigquery - 重复字符串

Question

我 splat 一个字符串，现在我想使用输出，但我得到错误，因为split考虑了 -output Repeated Sting，因此ARRAY.

我该如何解决它？

谢谢

With ex1 as (
SELECT 'test1,test2' as example
),

ex2 as (
SELECT 'test1' as str1, 5 as value)

SELECT * 
from

(SELECT split(example,',') as strings
from ex1)

left join 
    ex2
on str1 = strings

标签： google-bigquery

Answer 1

您需要 UNNEST 拆分的结果：

WITH 
ex1 AS (
SELECT 'test1,test2' as example),
ex2 AS (
SELECT 'test1' as str1, 5 as value)
SELECT * from
(SELECT * FROM UNNEST((SELECT split(example) FROM ex1)) as strings)
left join 
    ex2
on str1 = strings