时间戳

首页 > 解决方案 > Postgres加入数组类型导致数组列

sql - Postgres加入数组类型导致数组列

问题描述

我在这里看到过类似的问题，但我找到的解决方案似乎都没有给我我正在寻找的结果。

所以我有两张桌子：

Cards 表包含具有唯一 ID 和其他元数据（铸造成本等）的收藏卡：

Cards
| id INTEGER | name TEXT | ... |

Prints 表包含 Cards 表中卡片的打印信息（这是因为同一张卡片可以通过不同的卡片组打印成不同的版本）：

Prints
| id INTEGER | card_id INTEGER | set TEXT | ... |

我想做的是能够查询给定的卡片，但在单行响应中包含所述卡片的所有打印。我已经能够做到这一点，但如果一张卡片有 6 次打印，它将产生 6 行，每行包含所有打印信息作为 JSON 格式的字符串。

我一直在尝试使用的查询如下：

SELECT DISTINCT(cards.id), cards.*, array_agg(printings.*) FROM cards
LEFT JOIN printings ON cards.id=printings.card_id
WHERE cards.name="C1" 
GROUP BY cards.id,printings.id ORDER BY cards.id;

这导致输出如下（并且printing_information只是一个字符串）：

| id | name | ... |        printing_information            |
| 1  | C1   | ... | { printInfo1, printInfo2, printInfo3 } |
| 1  | C1   | ... | { printInfo1, printInfo2, printInfo3 } |
| 1  | C1   | ... | { printInfo1, printInfo2, printInfo3 } |
| 1  | C1   | ... | { printInfo1, printInfo2, printInfo3 } |
| 1  | C1   | ... | { printInfo1, printInfo2, printInfo3 } |
| 1  | C1   | ... | { printInfo1, printInfo2, printInfo3 } |

我想做的是将打印与卡片连接起来，这样响应就会产生：

| id | name | ... |        printing_information            |
|  1 | C1   | ... | { print1Info, print2Info, print3Info } |
|  2 | C2   | ... | { print1Info, print2Info }             |

是否值得在一个查询中完成所有这些操作？我可以通过一些小的后期处理来实现这一点，但这是否会像需要多个查询一样高效，并且不会那么漂亮，因为它可能用于多张卡片并且搜索多个 id 似乎很混乱。

任何帮助，将不胜感激！

标签： sqlarrayspostgresqljoin

解决方案

您需要先聚合prints表，然后加入cards表。

SELECT c.*, p.printing_information
FROM cards c
  LEFT JOIN (
    select pr.card_id, jsonb_agg(to_jsonb(pr))  as printing_information
    from printings pr
    group by pr.card_id 
  ) p ON c.id = p.card_id
WHERE c.name = 'C1'
ORDER BY c.id;

to_jsonb(pr)将printings表中的整行转换为 JSON 值。然后将其聚合到一个 JSON 数组中jsonb_agg()，用于卡片的所有打印。如果你想从 JSON 中删除一些列，例如 JSON 中的 thecard_id和 'id' 列，你可以使用to_jsonb(pr) - 'card_id' - 'id'

推荐阅读