如何將數組列中的項目與 Postgres 中的不同表匹配？

我在 Postgres 12 數據庫中有一個表，其中有一個包含一堆狗品種指南的數組列。我想查找這些值並基本上返回查找值而不是 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']

本質上，在返回值之前在我的“品種”表中查找它們。元素的順序不相關。

SELECT d.name, b.breeds_text
FROM   dogs d
CROSS  JOIN LATERAL (
  SELECT ARRAY(SELECT b.breed
               FROM   unnest(breeds) a(guid)
               JOIN   breeds b USING (guid)) AS breeds_text
  ) b;

因為我們JOIN在 之後unnnest()，元素的順序不一定保持不變。

我一開始有LEFT JOIN LATERAL (...) ON true。但是由於ARRAY建構子使子查詢總是只返回一行，這相當於一個更簡單的CROSS JOIN. 看：

• 如何結合聚合函式應用 ORDER BY 和 LIMIT？

為了保證元素的順序（如果需要？），在子查詢中使用WITH ORDINALITYand ：ORDER BY``LATERAL

SELECT d.name, b.breeds_text
FROM   dogs d
CROSS  JOIN LATERAL (
  SELECT ARRAY(SELECT b.breed
               FROM   unnest(breeds) WITH ORDINALITY AS a(guid, ord)
               JOIN   breeds b USING (guid)
               ORDER  BY a.ord) AS breeds_text
  ) b;

或者一個低相關的子查詢，可能會快一點：

SELECT d.name
    , ARRAY(SELECT b.breed
            FROM   unnest(d.breeds) WITH ORDINALITY AS a(guid, ord)
            JOIN   breeds b USING (guid)
            ORDER  BY a.ord) AS breeds_text
FROM   dogs d;

db<>在這裡擺弄

NULLvalues 和空數組在結果中產生一個空數組。要保留NULL，您需要做更多的事情。比如：使用CASE…

數組中的重複項將按照任一查詢的給定方式保留。

看：

• 如何保留未嵌套數組中元素的原始順序？
• 帶有元素編號的 PostgreSQL unnest()
• 具有多個值的數組列上的左外連接

或者考慮一個規範化的多對多關係設計而不是數組：

• 如何在 PostgreSQL 中實現多對多關係？

引用自：https://dba.stackexchange.com/questions/295284