雪花中的 ARRAY_REMOVE()? [英] ARRAY_REMOVE() in Snowflake?

问题描述

Postgresql 有 ARRAY_REMOVE 函数来从数组中删除元素.我怎样才能在雪花中做到这一点?

解决方案

UDF 方法效果很好，如果您愿意，可以使用另一种方法:

• 利用出色的

  同样的例子工作得很好.

   选择数组聚合(值)从表(扁平化(输入 => parse_json('[a"，b"，c"，a"]')))在哪里值不在('a')中；选择数组聚合(值)从表(扁平化(输入 => parse_json('[4, 1, 4, 2, 3, 4]')))在哪里值不在(4)中；选择数组聚合(值)从table(flatten(input => parse_json('[4.3, 1.1, 4.2, 0.1, 3.3, 0.2]')))在哪里值不在 (0.2) 中；

  Postgresql has the ARRAY_REMOVE function to remove elements from an array. How can I do this in Snowflake?

  解决方案

  The UDF approach works well, here's another approach if you want to:

  • Utilise the awesome snowflake caching that doesn't work if you use UDF's
  • Just write plain SQL
  • Ordering of the array is not guaranteed

  Depending on the use case ... If you're not likely to benefit from caching and want 'cleaner' looking code then the UDF might be best - however if you're likely benefit from caching, prefer just SQL then maybe this approach may be useful.

  FLATTEN the ARRAY -> Predicate data -> Return via ARRAY_AGG

  SELECT 
      ARRAY_AGG(VALUE) REMOVED
  FROM 
      CTE, LATERAL FLATTEN(AN_ARRAY) 
  WHERE 
      VALUE!='A'
  

  The same examples work just fine.

      select 
          array_agg(value) 
      from 
          table(flatten(input => parse_json('["a", "b", "c", "a"]'))) 
      where 
         value not in ('a');
      select 
          array_agg(value) 
      from 
          table(flatten(input => parse_json('[4, 1, 4, 2, 3, 4]'))) 
      where 
          value not in (4);
      select 
         array_agg(value) 
      from 
         table(flatten(input => parse_json('[4.3, 1.1, 4.2, 0.1, 3.3, 0.2]'))) 
      where 
         value not in (0.2);
  

  这篇关于雪花中的 ARRAY_REMOVE()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！