izveigor opened a new issue, #7072:
URL: https://github.com/apache/arrow-datafusion/issues/7072
### 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: | <b> Concept: </b> Replaces one occurrence of the
specified element with another specified element. With optional argument
replaces `max` occurrences of the specified element with another specified
element. |
| Sources: |
[Concept](https://github.com/apache/arrow-datafusion/discussions/6855)
|
## 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(array, from, to, max)` | `array_remove(array, element, max)` |
| Wholeness | `array_positions(array)` | `array_resize(x,
array_length(array))` | `array_replaces(array, from, to)` |
`array_removes(array, element)` |
<b>Example:</b>
### 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(array_remove([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(array_replace([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.
<b>StackOverFlow:</b>
-
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] |
+--------------------------------------------------------+
```
```
❯ select array_replace([1, 2, 2, 3, 2, 1, 4], 2, 5, 2);
+-----------------------------------------------------------------+
| array_replace(List([1,2,2,3,2,1,4]),Int64(2),Int64(5),Int64(2)) |
+-----------------------------------------------------------------+
| [1, 5, 5, 3, 2, 1, 4] |
+-----------------------------------------------------------------+
```
### Describe the solution you'd like
_No response_
### Describe alternatives you've considered
_No response_
### Additional context
_No response_
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
