google-bigquery - 大查询 : how to select multiple matching records from a column

Question

我需要编写一个查询来从列中获取匹配的记录。表 'abc' 有这个列 'category'。

mango
mango,orange
mango,orange,banana
pineapple
guava
grapes
guava,blueberry
.
.
.

想要的结果是这样的:

mango
mango,orange
mango,orange,banana
guava
guava,blueberry
.
.
.
.

我的代码是这样的:

Select * 
From abc
Where column_name like '%mango%' 
      and column name like '%guava%'

类别列有数千行，我无法对每一行进行硬编码。有什么办法可以获得所需的输出吗？提前致谢!

Answer 1

考虑以下选项

select * 
from your_table 
where exists   (
    select * from unnest(split(fruits)) fruit 
    where fruit in ('mango', 'guava')
  )    

或者

select distinct t.* 
from your_table t,  unnest(split(fruits)) fruit 
where fruit in ('mango', 'guava')        

如果应用于您问题中的样本数据 - 输出是