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:

CREATEOR REPLACE FUNCTION array_remove_js(ARR variant, VAL variant)
returnsarraylanguage javascript
as'
return ARR.filter(function(item) {
    return item !== VAL
})
';

Testing with strings, integers, and floats:

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

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

selectarray_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

SELECTARRAY_AGG(VALUE) REMOVED
FROM 
    CTE, LATERAL FLATTEN(AN_ARRAY) 
WHEREVALUE!='A'

The same examples work just fine.

selectarray_agg(value) 
    fromtable(flatten(input => parse_json('["a", "b", "c", "a"]'))) 
    wherevaluenotin ('a');
    selectarray_agg(value) 
    fromtable(flatten(input => parse_json('[4, 1, 4, 2, 3, 4]'))) 
    wherevaluenotin (4);
    selectarray_agg(value) 
    fromtable(flatten(input => parse_json('[4.3, 1.1, 4.2, 0.1, 3.3, 0.2]'))) 
    wherevaluenotin (0.2);