CODEWITHSUNDEEP
sqlarrayspostgresql
johan855
johan855

Reputation: 1626

Making an organized distinct element array in PostgreSQL

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;

Upvotes: 0

Views: 41

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 1

Related Questions