Объединение элементов массива в объединенную таблицу PostgreSQL

Можно ли выполнить конкатенацию массива элементов 1 на 1, если у меня есть такой запрос:

РЕДАКТИРОВАТЬ: массивы не всегда содержат одинаковое количество элементов. может быть, что array1 иногда имеет 4 элемента и array2 8 элементов.

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

Я хотел бы получить здесь парную конкатенацию обоих массивов 1 и 2, например:

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

Я пробовал использовать unnest, но не могу заставить его работать:

    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
;

РЕДАКТИРОВАТЬ:

Это работает только для одной таблицы:

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

++ Благодаря https://stackoverflow.com/users/1463595/%D0%9D%D0%9B%D0%9E

РЕДАКТИРОВАТЬ:

Я пришел к очень близкому решению, но оно смешивает некоторые промежуточные значения после того, как конкатенация между массивами выполнена, тем не менее, мне все еще нужно идеальное решение ...

Подход, который я сейчас использую:

1) Создание одной таблицы на основе двух отдельных 2) агрегирование с использованием 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;

postgresql arrays concatenation unnest
person johan855    schedule 26.03.2016    source источник
comment
Боковое примечание: правильный JSON с ", а не с '.   -  person maraca    schedule 26.03.2016
comment
В PostgreSQL вы также можете определить строку, содержащую массивы, как: select array ['a', 'b', 'c'] as array1;   -  person johan855    schedule 26.03.2016

Ответы (2)


arrow_upward
1
arrow_downward

Что-то вроде этого.

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);

Оператор соединения using (..) автоматически принимает ненулевое значение из объединенных таблиц. Это устраняет необходимость использования, например, coalesce(a.id, b.id).n

Это некрасиво и определенно неэффективно для больших таблиц, но, похоже, делает все, что вы хотите.

Для массивов, которые не имеют одинакового количества элементов, результатом будет только элемент из одного из массивов.

Для этого набора данных:

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']);

Он возвращает такой результат:

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

arrow_upward
0
arrow_downward

Я думаю, вы слишком далеко зашли, попробуйте это (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
;
person maraca    schedule 26.03.2016
comment
Я отредактировал вопрос. Проблема в том, что у меня не всегда одинаковое количество элементов в каждом массиве. - person johan855; 26.03.2016
comment
Вы хотите объединять только массивы с равным количеством элементов? Это некрасиво, но вы можете сделать то же самое для 4 и 8 элементов, если. - person maraca; 26.03.2016
comment
Если это ['a', 'b', 'c', 'd'] с ['x', 'y', 'z'], результат должен быть ['a-x', 'b-y' , 'c-z', 'd-'] или ['a-x', 'b-y', 'c-z', 'd-null']] - person johan855; 26.03.2016