在snowflake中查询一个数组的子集

2 人关注

我试图在snowflake中根据其中一列的数组元素对SQL查询进行子集，但不确定如何做到这一点。

例如，如果列2是一个数组数据类型，看起来像这样的

SELECT column2
FROM table
LIMIT 7;
Row column2
1 ["cats","dogs"]
2 ["horses","cows","cats"]
3 NULL
4 ["dogs","fish]
5 ["birds"]
6 ["cats"]
7 NULL
我想对数据进行子集，并运行一个查询，在第2列的任何一个数组中拉出有 "猫 "的元素的任何一行--即第1，2，6行，我将如何构建这个查询？
使用类似这样的东西是行不通的。
SELECT column1, column2, column3
FROM Table
WHERE column2 = "cats" (or using an IN statement)
并导致一个错误信息，即 "cats "的标识符无效，我希望它是在一个数组中。
如果您有任何见解，我们将不胜感激。

sql


         
          snowflake-cloud-data-platform


          
           
            
            
             Gordon Linoff
            
           
           
            发布于
            
            2021-07-07


          
           
            你想
            
             
              array_contains()
             
            
            :
           
           where array_contains('cats'::variant, column2)


           
            
             
              因此，当我在实际查询中这样做时，我得到
              
               SQL compilation error: line 6 at position 4 invalid argument types for function 'ARRAY_CONTAINS: (VARCHAR(9), ARRAY).
              
              ，数组内的数据是用双引号表示的，如果这有什么不同。


           
            
             
              Felipe Hoffa
             
             ：


           
            
             
              
               array_contains
              
              将会工作，但你需要注意类型，见
              
               stackoverflow.com/a/68278871/132438


          
           
            
             
             
              Felipe Hoffa
             
            
            
             发布于
             
             2021-07-07


          
           
            已采纳


          
           
            
             
              ARRAY_CONTAINS()
             
             工作，但你必须小心处理类型问题。
            
            
             例如，这个返回false。
            
            select array_contains('2020-01-01'::date::variant
    , array_construct('2020-01-01', '2019-01-01'));
但这些都返回真。
select array_contains('2020-01-01'::date::string::variant
    , array_construct('2020-01-01', '2019-01-01'));
select array_contains('2020-01-01'::date::variant
    , array_construct('2020-01-01'::date, '2019-01-01'));
在字符串的情况下，这个会返回一个编译错误（正如你所看到的）。
select array_contains('cats'
    , array_construct('cats', 'dogs'));
-- SQL compilation error: error line 1 at position 7 Invalid argument types for function 'ARRAY_CONTAINS': (VARCHAR(4), ARRAY)
但这个可以解决这个问题。
select array_contains('cats'::variant
    , array_construct('cats', 'dogs'));


           
            
             
              
               
                
                 
                  好吧，有意思。那么，
                  
                   ::variant
                  
                  表示数组中还有其他元素？我是否必须明确声明它们都是什么，例如在语句
                  
                   array_construct('cats', 'dogs'))
                  
                  ？这使得查询变得很困难，当不同的行有不同的元素集的数组，包括'猫'以及
                  
                   ["horses","cows","cats"]
                  
                  ，或者只是
                  
                   ["cat"]
                  
                  ，或者就许多列而言，我实际上不知道哪些其他元素是猫的。


           
            
             
              
               
                
                 
                  我很抱歉，我的意思是 "猫 "后面有一个 "s"。我并没有打算让这些词有一些是单数，有一些是复数，它们的意思都是一样的，都是复数。我对这种混淆表示歉意。


           
            
             
              
               
                
                 
                  Felipe Hoffa
                 
                 ：


           
            
             
              
               
                
                 
                  我不确定这个问题是什么意思。我使用
                  
                   array_construct
                  
                  ，因为我没有一个像你已经有的带数组的表。你试过
                  
                   where array_contains('cats'::variant, column2)
                  
                  吗？


           
            
             
              
               
                
                 
                  最后一个问题。是否有可能在包括 "猫 "的同时排除另一个元素，如 "马"？因此，创建一个查询，包括第1行["猫"，"狗"]和第6行["猫"]，但不包括第2行，因为你不希望任何有 "马 "的行，即使 "猫 "是该阵列的一部分？


           
            
             
              
               
                
                 
                  Felipe Hoffa
                 
                 ：


           
            
             
              
               
                
                 
                  让我们把它作为一个新的问题;)


          
           
            
             
              
               
                
                 
                 
                  Adrian White
                 
                
                
                 发布于
                 
                 2021-07-07


          
           
            
             
              
               
                
                 array_contains可以让你回答你的具体问题，然而我认为看看你如何将数组转化为看起来更像一个表的东西可能是有用的。
                
                
                 如果你正在使用数组，snowflake 中的横向
                 
                  扁平化
                 
                 函数绝对值得一看。
                
                with cte as (
  select 'some other info_1' col_1 ,ARRAY_CONSTRUCT('cats','dogs') col_2  
union select 'some other info_2' col_1 ,ARRAY_CONSTRUCT('horses','cows','cats')
union select 'some other info_3' col_1 ,NULL
union select 'some other info_4' col_1 ,ARRAY_CONSTRUCT('dogs','fish')
union select 'some other info_5' col_1 ,ARRAY_CONSTRUCT('birds')
union select 'some other info_6' col_1 ,ARRAY_CONSTRUCT('cats')