Implement array_replace function

[izveigor]

Is your feature request related to a problem or challenge?

Summary

Characteristic	Description
Function name:	array_replace
Aliases:	list_replace
Original function?:	Yes
Function Description:	Concept: Replaces the first occurrence of the specified element with another specified element.
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_replace([1, 2, 2, 3, 2, 1, 4], 2, 5);
+--------------------------------------------------------+
| array_replace(List([1,2,2,3,2,1,4]),Int64(2),Int64(5)) |
+--------------------------------------------------------+
| [1, 5, 2, 3, 2, 1, 4]                                  |
+--------------------------------------------------------+

Describe the solution you'd like

No response

Describe alternatives you've considered

No response

Additional context

No response