ARRAY_REMOVE() in Snowflake?

Question

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

Answer 1

Snowflake has this function ARRAY_REMOVE()

Answer 2

It is possible to remove elements from ARRAY using either by element ARRAY_REMOVE or element's position ARRAY_REMOVE_AT:

SHOW BUILTIN FUNCTIONS LIKE 'ARRAY_REMOVE%';

name	arguments
ARRAY_REMOVE	ARRAY_REMOVE(ARRAY, VARIANT) RETURN ARRAY
ARRAY_REMOVE_AT	ARRAY_REMOVE_AT(ARRAY, NUMBER) RETURN ARRAY

---

Usage:

SELECT ARRAY_REMOVE(['a', 'b', 'c'], 'b'::VARIANT);
-- [   "a",   "c" ]

SELECT ARRAY_REMOVE_AT(['a', 'b', 'c'], 1);
-- [   "a",   "c" ]

Output: