postgres - aggregate items in text array

Question

I have an array of text items, each text item in the array is a delimited string, here is a sample:

drop table if exists tmp;
create table tmp(x text);
insert into tmp select ('1~41.5~50~1|2~43.72~1000~1|3~52.0~1~1|4~57.5~7500~1|5~68.0~8~1|6~80.95~6~1|7~84.25~300~1');

with t as (
    select string_to_array(x , '|') arr 
    from  tmp
)
select * , cardinality(arr) arr_len , split_part(arr[1],'~',2)
from t

sample output:

"{1~41.5~50~1,2~43.72~1000~1,3~52.0~1~1,4~57.5~7500~1,5~68.0~8~1,6~80.95~6~1,7~84.25~300~1}";7;"41.5"

I'm interested in extracting the price, which is represented as the second item of each array entry (when delimited by ~), so that the output would be:

41.5,43.72,52.0,...

when using split_part, that return only one result of a specific array index, and I do not want to hard-code all indexes, since it varies.

suggestions are appreciated.

GMB · Accepted Answer · 2020-09-08 12:56:52Z

1

Here is one approach:

select t.*, w.prices
from tmp t
cross join lateral (
    select array_agg(split_part(v.z, '~', 2)) prices
    from regexp_split_to_table(t.x, '\|') as v(z)
) w

Within the subquery, we split the string to rows, using separator |; then, we aggregate the second element of each row back into an array.

You can be explicit that you want to preserve the ordering of the elements with with ordinality:

select t.*, w.prices
from tmp t
cross join lateral (
    select array_agg(split_part(v.z, '~', 2) order by v.n) prices
    from regexp_split_to_table(t.x, '\|') with ordinality as v(z, n)
) w

edited Sep 8, 2020 at 12:56

answered Sep 8, 2020 at 12:48

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Y.S Over a year ago

awsome, i actually just managed to get something using: split_part(unnest(arr),'~',2) price followed by a string_agg. Does this gurenttee the elements will return in the same order as the original array sequence?

GMB Over a year ago

@Y.S: see my update about how to guarantee the ordering of the elements in the results...

Collectives™ on Stack Overflow

postgres - aggregate items in text array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related