Making an organized distinct element array in PostgreSQL

Question

Does anyone know how it can be possible to:

Unnest a postgres array Create a new array as a "select distinct" from that unnested array (So no elements are repeated) having the new array ordered in the way it was before?

Example

I have the following 3 columns:

order_id, channel_path, hit_timestamp_path
    x      a,b,b,c,d      1,2,3,4,5
    y      q,w,e,r,r      2,3,4,5,6
    .
    .
    .

And I want to end up with the following:

order_id, channel_path
    x      a,b,c,d      
    y      q,w,e,r   
    .
    .
    .

The issue is that the way I am doing it, I end up with something like this:

order_id, channel_path
    x      b,a,c,d      
    y      w,r,e,q   
    .
    .
    .

The initial solution I am trying is the following:

SELECT order_id, (
                  SELECT array_agg(unnested_channels)
                  FROM (
                        SELECT DISTINCT unnested_channels
                        FROM (
                              SELECT unnest(channel_path) AS unnested_channels,
                                     unnest(hit_timestamp_path) as unnested_timestamps
                                     order by 2 asc
                              ) AS unnested_channels_table
                       ) AS distinct_unnested_channels_table
                  ) AS distinct_channel_path
from dim_conversion_path;

What do you need the hit_timestamp_path for? Do you need that in the output? Or do you just need order_id, channel_path? — user330315
– user330315, Commented Mar 15, 2016 at 21:37
It's not needed, I only put it there to show that there is an array which indicates the time of each hit. — johan855
– johan855, Commented Mar 15, 2016 at 21:50

score 1 · Accepted Answer · 2016-03-15 22:15:37Z

1

Something like this:

select order_id, array_agg(x order by rn) as unique_channel_path
from (
  select distinct on (order_id, a.x) order_id, a.x, a.rn
  from dim_conversion_path, unnest(channel_path) with ordinality as a (x,rn)
  order by 1,2,3  
) t
group by order_id

edited Mar 15, 2016 at 22:15

answered Mar 15, 2016 at 21:10

user330315

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

1 Comment

johan855 Over a year ago

It worked perfectly, thanks as well for introducing the ordinality concept, it-äs the first time i see it.

Collectives™ on Stack Overflow

Making an organized distinct element array in PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related