topic Re: Is it possible to control the ordering of the array values created by array_agg()? in Data Engineering

Is it possible to control the ordering of the array values created by array_agg()?

akisugi — Sat, 06 Apr 2024 14:12:37 GMT

Hi! I would be glad to ask you some questions.

I have the following data.

I would like to get this kind of result. I want `move` to correspond to the order of `hist`.

Therefore, i considered the following query.

```

with tmp as (

select * from (values

(1, 1, 'a'),

(1, 3, 'c'),

(1, 2, 'b'),

(2, 1, 'a'),

(2, 2, 'b'),

(2, 3, null),

(3, 3, 'b'),

(3, 1, 'a'),

(3, 2, null),

(4, 1, 'a')

) as tab(custid, hist, alf)

order by custid asc, hist asc

)

select

custid,

array_join(array_agg(alf), ' -> ') as move

from tmp

group by custid

order by custid asc;

```

Tried sorting by `order by` before combining strings into an array with `array_join(array_agg())`. At first glance it seems to work, but the official documentation for `array_agg()` states the following

```
The order of elements in the array is non-deterministic.

From | https://docs.databricks.com/en/sql/language-manual/functions/array_agg.html#:~:text=The%20order%20of%20elements%20in%20the%20array%20is%20non%2Ddeterministic.%20NULL%20values%20are%20excluded.
```

Does it make sense to sort by `order by` before running `array_join(array_agg())`? Will it work as expected with larger data sizes?

I apologize for the inconvenience and would appreciate your response.

Re: Is it possible to control the ordering of the array values created by array_agg()?

ThomazRossito — Sun, 07 Apr 2024 15:38:03 GMT

Hi,

I believe that for your scenario the "sort_array", function can help

https://docs.databricks.com/en/sql/language-manual/functions/sort_array.html

Re: Is it possible to control the ordering of the array values created by array_agg()?

akisugi — Tue, 09 Apr 2024 12:14:47 GMT

Hi @ThomazRossito

Thanks for your response and Sorry for my late reply.

I apologize that my question did not accurately describe my scenario.

It is true that the `sort_array` function works well for this sample data.

On the other hand, array values do not necessarily have rules like alphabetical or numeric.

------------------------------

with tmp as (
select * from (values
(1, 1, 'cherry'),
(1, 3, 'strawberries'),
(1, 2, 'acerola'),
(1, 5, 'banan'),
(1, 4, 'lemon')
) as tab(custid, hist, alf)
order by custid asc, hist asc
)
select
custid,
array_join(array_agg(alf), ' -> ') as move
from tmp
group by custid
order by custid asc;

------------------------------

I would like to get array like `[cherry -> acerola -> strawberries -> lemon -> banan]` for above sample data. I would like to sort and keep the array values according to the order of the `hist` columns but `array_agg()` states `The order of elements in the array is non-deterministic`.

If you have a solution, I would appreciate your response!

Re: Is it possible to control the ordering of the array values created by array_agg()?

ThomazRossito — Tue, 09 Apr 2024 23:11:06 GMT

Hello,

Below is a solution
regexp_replace(array_join(sort_array(array_agg(concat(hist, alf))), ' -> '),'[0-9]','')

The idea in the code is to concatenate the "hist" column with the "alf" column, this way the "sort_array" can sort based on the numbers in the "hist" column and finally a "regexp_replace" is done removing the numbers from the "hist" column. hist" as they cannot be shown in the final result

Hope this helps

Re: Is it possible to control the ordering of the array values created by array_agg()?

akisugi — Wed, 10 Apr 2024 00:25:45 GMT

Hi @ThomazRossito

This is a great idea. It can solve my problem.Thank you.

Re: Is it possible to control the ordering of the array values created by array_agg()?

akisugi — Wed, 10 Apr 2024 00:26:29 GMT

Hi @ThomazRossito

This is a great idea. It can solve my problem.Thank you.