Concatenate array elements on a joined table PostgreSQL
<p>Is it possible to do a 1 on 1 element array concatenation if I have a query like this:</p>

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

<pre><code>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
</code></pre>

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

<pre><code>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    ...
</code></pre>

<p>I tried using unnest but i can't make it work:</p>

<pre><code>    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
;
</code></pre>

<p><strong>EDIT:</strong></p>

<p>This works for only one table:</p>

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

<p>++Thanks to <a href="https://stackoverflow.com/users/1463595/%D0%9D%D0%9B%D0%9E">https://stackoverflow.com/users/1463595/%D0%9D%D0%9B%D0%9E</a></p>

<p><strong>EDIT:</strong></p>

<p>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...</p>

<p>The approach I am now using is:</p>

<p>1) Creating one table based on the 2 separate ones
2) aggregating using Lateral:</p>

<pre><code>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;
</code></pre>