CODEWITHSUNDEEP
phpsqlarrayssnowflake-cloud-data-platformarray-difference
lucas winter
lucas winter

Reputation: 191

Finding the Difference By Row Between 2 Columns that are Both Arrays in SnowSql

I have a dataset that is comprised of a date and two other columns that are in array format. I am trying to find all the values in array_1 that are not in array_2.

Date | Array_1     | Array_2
-------------------------
1/20 | [1,2,3]     | [1,2]
2/20 | [4,5,6]     | [[1,2,4]

Desired Output:

    Date | Array_1     
    --------------
    1/20 | [3]    
    2/20 | [5,6]

Upvotes: 0

Views: 160

Answers (2)

Strawberry
Strawberry

Reputation: 33945

Just to remind that if you were to use application code for this, it might be as simple as (using PHP for this example):

$array1 = array(4,5,6);
$array2 = array(5,6,7);

print_r(array_diff($array1, $array2));

Outputs: Array ( [0] => 4 )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270421

The idea is:

  • Unnest ("flatten") the values into two tables.
  • Use set functions for the operation you want.
  • Re-aggregate to an array.

I don't have Snowflake on hand, but I think this is how it works:

select t.*, array_3
from t left join lateral
     (select array_agg(el) as array_3
      from (select array_1
            from table(flatten(input ==> t.array_1)) a1
            except
            select array_2
            from table(flatten(input ==> t.array_2)) a2
           ) x
     ) x

Upvotes: 1

Related Questions