PostgreSQL JOIN с типом массива с порядком элементов массива, как реализовать?

Question

У меня есть две таблицы в базе данных:

CREATE TABLE items(
 id SERIAL PRIMARY KEY,
 ... some other fields
);

Эта таблица содержит строку данных о прибытии с уникальным идентификатором.

CREATE TABLE some_chosen_data_in_order(
 id SERIAL PRIMARY KEY,
 id_items INTEGER[],

);

Эта таблица содержит поле типа массива. Каждая строка содержит значения идентификаторов из таблицы items в определенном порядке. Например: {2,4,233,5},

Теперь я хочу получить данные из таблицы items для выбранной строки из таблицы some_chosen_data_in_order с порядком для элементов в типе массива.

Моя попытка была JOIN:

SELECT I.* FROM items AS I 
JOIN some_chosen_data_in_order AS S ON I.id = ANY(S.id_items) WHERE S.id = ?

Вторая попытка была подзапросом вроде:

SELECT I.* FROM items AS I 
WHERE I.id = ANY 
(ARRAY[SELECT S.id_items FROM some_chosen_data_in_order  WHERE id = ?])

Но ни один из них не хранит идентификаторы в том же порядке, что и в поле массива. Не могли бы вы помочь мне, как получить данные от items таблица соответствует порядку идентификаторов массива из some_chosen_data_in_order таблица для конкретной строки?

Answer 1

SELECT t.*
FROM unnest(ARRAY[1,2,3,2,3,5]) item_id
LEFT JOIN items t on t.id=item_id

Приведенный выше запрос выбрать элементы из items Таблица с идентификаторами: 1,2,3,2,3,5 в указанном порядке.

Answer 2

Наверное, нормализация вашего стола была бы лучшим советом, который я могу вам дать.

Модуль contrib int_array имеет функцию idx, которая выдаст вам позицию индекса int в массиве. Также в вики-фрагментах есть функция idx, которая работает с массивами любых типов данных.

SELECT i.*, idx(id_items, i.id) AS idx
FROM some_chosen_data_in_order s
JOIN items i ON i.id = ANY(s.id_items)
ORDER BY idx(id_items, i.id)

Answer 3

select distinct on (some_chosen_data_in_order.id)
  some_chosen_data_in_order.*,
   array_to_json( array_agg(row_to_json( items))
  over ( partition by some_chosen_data_in_order.id ))
from some_chosen_data_in_order
  left join items on items.id = any (some_chosen_data_in_order.id_items)

Answer 4

Если вам не нравятся функции ( /questions/40143397/postgresql-join-s-tipom-massiva-s-poryadkom-elementov-massiva-kak-realizovat/40143406#40143406):

      SELECT jt.*
FROM (
     SELECT ARRAY_AGG(DISTINCT dupes_column) AS unique_vals
     FROM dupes_table
) q
LEFT JOIN joined_table jt
ON jt.id = ANY(q.unique_vals);

Answer 5

SELECT I.* FROM items AS I 
WHERE I.id IN (SELECT UNNEST(id_items) FROM some_chosen_data_in_order 
(ARRAY[SELECT S.id_items FROM some_chosen_data_in_order  WHERE id = ?])