Concatenate array elements on a joined table PostgreSQL

Question

Is it possible to do a 1 on 1 element array concatenation if I have a query like this:

EDIT: Arrays not always have the same number of elements. could be that array1 has sometimes 4 elements ans array2 8 elements.

drop table if exists a;
drop table if exists b;
create temporary table a as (select 1 as id,array['a','b','c'] as array1);
create temporary table b as (select 1 as id,array['x','y','z'] as array2);

select
a.id,
a.array1,
b.array2,
array_concat--This has to be a 1 to 1 ordered concatenation (see                   
            --example below)
from a
left join b on b.id=a.id

What I would like to obtain here is a paired concatenation of both arrays 1 and 2, like this:

id       array11          array2         array_concat
 1    ['a','b','c']   ['d','e','f']   ['a-d','b-e','c-f']
 2    ['x','y','z']   ['i','j','k']   ['x-i','y-j','z-k']
 3    ...

I tried using unnest but i can't make it work:

    select
    a.id,
    a.array1,
    b.array2,
    array_concat
    from table a
    left join b on b.id=a.id
    left join (select a.array1,b.array2, array_agg(a1||b2)
                      FROM unnest(a.array1, b.array2) 
                           ab (a1, b2)
              ) ag on ag.array1=a.array1 and  ag.array2=b.array2
;

EDIT:

This works for only one table:

SELECT array_agg(el1||el2)
FROM unnest(ARRAY['a','b','c'], ARRAY['d','e','f']) el (el1, el2);

++Thanks to https://stackoverflow.com/users/1463595/%D0%9D%D0%9B%D0%9E

EDIT:

I came to a very close solution but it mixes up some of the intermediate values once the concatenation between arrays is done, never the less I still need a perfect solution...

The approach I am now using is:

1) Creating one table based on the 2 separate ones 2) aggregating using Lateral:

create temporary table new_table as
SELECT
    id,
    a.a,
    b.b
    FROM a a
    LEFT JOIN b b on a.id=b.id;

SELECT id,
       ab_unified
       FROM pair_sources_mediums_campaigns,
       LATERAL (SELECT ARRAY_AGG(a||'[-]'||b order by grp1) as ab_unified
               FROM (SELECT DISTINCT case when a null
                                   then 'not tracked'
                                   else a
                                   end as a
                         ,case when b is null
                                   then 'none'
                                   else b
                                   end as b
                            ,rn - ROW_NUMBER() OVER(PARTITION BY a,b ORDER BY rn) AS grp1

                    FROM unnest(a,b) with ordinality as el (a,b,rn)
                ) AS sub
           ) AS lat1
           order by 1;

In PostgreSQL you can also define string containing arrays as: select array['a','b','c'] as array1; — johan855
– johan855, Commented Mar 26, 2016 at 0:46

score 2 · Accepted Answer · 2016-03-26 15:35:07Z

Something like this.

with a_elements (id, element, idx) as (
  select a.id,
         u.element, 
         u.idx
  from a 
    cross join lateral unnest(a.array1) with ordinality as u(element, idx)
), b_elements (id, element, idx) as (
  select b.id,
         u.element, 
         u.idx
  from b
    cross join lateral unnest(b.array2) with ordinality as u(element, idx)
)
select id,
       array_agg(concat_ws('-', a.element, b.element) order by idx) as elements
from a_elements a
  full outer join b_elements b using (id, idx)
group by coalesce(a.id, b.id);

The join operator using (..) will automatically take the non-null value from the joined tables. This removes the need to use e.g. coalesce(a.id, b.id).n

It's not pretty and definitely not efficient for large tables, but seems to do all you want.

For arrays that do not have the same amount of elements, the result will only have the element from one of the arrays.

For this dataset:

insert into a 
  (id, array1)
values
  (1, array['a','b','c','d']),
  (2, array['d','e','f']);

insert into b 
  (id, array2)
values 
  (1, array['x','y','z']), 
  (2, array['m','n','o','p']);

It returns this result:

id | elements       
---+----------------
 1 | {a-x,b-y,c-z,d}
 2 | {d-m,e-n,f-o,p}

maraca · Accepted Answer · 2016-03-26 01:23:16Z

1

I think you were thinking too far, try this (SQLFiddle):

select
   a.id,
   a.array1,
   b.array2,
   array[a.array1[1] || '-' || b.array2[1],
         a.array1[2] || '-' || b.array2[2],
         a.array1[3] || '-' || b.array2[3]] array_concat
from
   a inner join
   b on b.id = a.id
;

edited Mar 26, 2016 at 1:23

answered Mar 26, 2016 at 1:07

maraca

9,5873 gold badges27 silver badges50 bronze badges

3 Comments

johan855 Over a year ago

I edited the question, The issue is I not always have the same amount of elements in each array.

maraca Over a year ago

Do you want to join only the arrays with an equal amount of elements? It's ugly, but you could do the above for 4 and 8 elements with a case when.

johan855 Over a year ago

If it is ['a','b','c','d'] with ['x','y','z'] the result should be ['a-x','b-y','c-z','d-'] or ['a-x','b-y','c-z','d-null']]

Collectives™ on Stack Overflow

Concatenate array elements on a joined table PostgreSQL

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related