Как я могу сопоставить элементы в столбце массива с другой таблицей в Postgres?

# #postgresql #google-bigquery

Вопрос:

У меня есть таблица, в которой есть столбец массива с кучей идентификаторов породы собак. Я хочу найти эти значения и в основном возвращать значения поиска вместо идентификаторов guid.

 CREATE TABLE dogs(
 name text,
 breeds text[]
);

INSERT INTO dogs (name, breeds) VALUES ('Barkley', '{"abc", "xyz"}');
INSERT INTO dogs (name, breeds) VALUES ('Ponyo', '{"zzz", "xyz"}');

CREATE TABLE breeds(
 guid text,
 breed text
);


INSERT INTO breeds (guid, breed) VALUES ('abc', 'Maltipoo');
INSERT INTO breeds (guid, breed) VALUES ('xyz', 'Jack Russel');
INSERT INTO breeds (guid, breed) VALUES ('zzz', 'Dalmatian');

Я хотел бы иметь возможность вернуть следующее:

 Barkley, ['Maltipoo', 'Jack Russel']
Ponyo, ['Jack Russel', 'Dalmatian']

По сути, посмотрите их в моей таблице «породы», прежде чем возвращать значения.

Ответ №1:

Злоупотребляя массивами таким образом, вам сначала нужно преобразовать их в наборы с помощью unnest() .

 SELECT d.name,
       concat('[',
              string_agg(concat('''',
                                b.breed,
                                ''''),
                         ', '),
              ']')
       FROM dogs d
            CROSS JOIN LATERAL unnest(d.breeds) db
                                                (b)
            LEFT JOIN breeds b
                      ON b.guid = db.b
       GROUP BY d.name;

db<>скрипка

Но что вам действительно следует сделать, так это прекратить злоупотреблять массивами и нормализовать схему с помощью таблицы ссылок.

 CREATE TABLE dogs
             (name text,
              PRIMARY KEY (name));

CREATE TABLE breeds
             (guid text,
              breed text,
              PRIMARY KEY (guid));

CREATE TABLE dogs_breeds
             (dog text,
              breed text,
              PRIMARY KEY (dog,
                           breed),
              FOREIGN KEY (dog)
                          REFERENCES dogs
                                     (name),
              FOREIGN KEY (breed)
                          REFERENCES breeds
                                     (guid));
                                      
INSERT INTO dogs
            (name)
            VALUES ('Barkley'),
                   ('Ponyo');

INSERT INTO breeds
            (guid,
             breed) VALUES ('abc',
                            'Maltipoo'),
                           ('xyz',
                            'Jack Russel'),
                           ('zzz',
                            'Dalmatian');

INSERT INTO dogs_breeds
            (dog,
             breed)
            VALUES ('Barkley',
                    'abc'),
                   ('Barkley',
                    'xyz'),
                   ('Ponyo',
                    'zzz'),
                   ('Ponyo',
                    'xyz');

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

Затем вы можете просто присоединиться и объединить, чтобы получить свой результат.

 SELECT d.name,
       concat('[',
              string_agg(concat('''',
                                b.breed,
                                ''''),
                         ', '),
              ']')
       FROM dogs d
            LEFT JOIN dogs_breeds db
                      ON db.dog = d.name
            LEFT JOIN breeds b
                      ON b.guid = db.breed
       GROUP BY d.name;

db<>скрипка

Ответ №2:

Ниже приведен стандартный SQL для BigQuery

 select name, array(
    select breed from t.breeds guid  
    join breeds using(guid)
  ) breeds
from dogs t

Если обратиться к образцам данных в вашем вопросе:

Стол dogs

и стол breeds

на выходе получается