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;

Answer 1

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}

Answer 2

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
;