Implement array_remove_n function

[izveigor]

Is your feature request related to a problem or challenge?

Summary

Characteristic	Description
Function name:	array_remove_n
Aliases:	list_remove_n
Original function?:	Yes
Function Description:	Concept: Removes the first max elements from the array equal to the given value.
Sources:	Concept

Why innovation is needed:

I think it will be easier for users to modify arrays using the full table of values (rather than looking up indexes):

Quantity	Search	Fill	Replacement	Remove
Uniqueness	array_position(array, [i])	array_resize(x, 1)	array_replace(array, from, to)	array_remove(array, element)
Plurality	array_positions(array)[:i]	array_resize(x, n)	array_replace_n(array, from, to, max)	array_remove_n(array, element, max)
Wholeness	array_positions(array)	array_resize(x, array_length(array))	array_replace_all(array, from, to)	array_remove_all(array, element)

Example:

Example with stones

Let's say in some country there is a vote for the approval of some reform. Every citizen can vote for and against. The color of the stones will be used as the subject of the vote, namely black (against) and white (for). From the point of view of the array, this reconstruction will look like this, we have an array consisting of 0 (black color) and 1 (white color). However, a situation may arise the elections. For example, 2 white balls and 3 black balls were dishonestly counted in the vote. Having noticed the falsification, the state decides to remove or replace these stones with NULL.

Now let's try to solve these problems using different databases:

Initial list: [1, 1, 1, 1, 1, 1, 0, 0, 0, 1, 1, 0]

My decision

# Remove
SELECT array_remove_n(array_remove_n([1, 1, 1, 1, 1, 1, 0, 0, 0, 1, 1, 0], 1, 3), 0, 2);
----
[1, 1, 1, 0, 1, 1, 0]

# Replace
SELECT array_replace_n(array_replace_n([1, 1, 1, 1, 1, 1, 0, 0, 0, 1, 1, 0], 1, NULL, 3), 0, NULL, 2);
----
[NULL, NULL, NULL, 1, 1, 1, NULL, NULL, 0, 1, 1, 0]

Other databases

There are no solutions using Turing incomplete declarative language (classic SQL) for databases like DuckDB, PostgreSQL.

StackOverFlow:

• https://stackoverflow.com/questions/54992228/remove-one-non-unique-value-from-an-array
• https://stackoverflow.com/questions/59734581/how-to-remove-specific-value-from-array
• https://stackoverflow.com/questions/58535121/how-to-remove-first-occurence-of-an-element-in-array-in-clickhouse
• https://stackoverflow.com/questions/63287790/set-array-value-at-specific-index-in-clickhouse

Examples:

❯ select array_remove_n([1, 2, 2, 3, 2, 1, 4], 2, 2);
+---------------------------------------------------------+
| array_remove_n(List([1,2,2,3,2,1,4]),Int64(2),Int64(2)) |
+---------------------------------------------------------+
| [1, 3, 2, 1, 4]                                         |
+---------------------------------------------------------+

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response