Array_remove() In Snowflake?

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

Solution 1:

You can create a JS UDF. For it to work with any types, you'll need to cast the value to be removed to variant:

CREATE OR REPLACE FUNCTION array_remove_js(ARR variant, VAL variant)
returns array 
language javascript
as '
return ARR.filter(function(item) {
    return item !== VAL
})
';

Testing with strings, integers, and floats:

select array_remove_js(parse_json('["a", "b", "c", "a"]'), 'a'::variant);

select array_remove_js(parse_json('[4, 1, 4, 2, 3, 4]'), 4::variant);

select array_remove_js(parse_json('[4.3, 1.1, 4.2, 0.1, 3.3, 0.2]'), 0.2::variant);

Solution 2:

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);