Postgres多对多关联查询：将关联标签合并为数组列

问题背景 #

现有图片与标签的多对多架构：

• ImageContent表存储图片信息
• ContentTags表存储标签
• ImageTags为关联中间表

期望查询图片数据时，将关联的标签名合并到单个列中，但当前执行的SQL会返回每个标签对应一行数据，直接添加GROUP BY会报错，需要正确的SQL实现方案。

现有表结构 #

-- 图片表
CREATE TABLE ImageContent (
    Id SERIAL PRIMARY KEY,
    Title TEXT NOT NULL,
    Description TEXT,
    ImageUrl TEXT NOT NULL,
    CreationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 标签表
CREATE TABLE ContentTags (
    Id SERIAL PRIMARY KEY,
    TagName TEXT UNIQUE NOT NULL
);

-- 图片与标签的多对多关联表
CREATE TABLE ImageTags (
    imageId INTEGER,
    TagID INTEGER,
    FOREIGN KEY (imageID) REFERENCES ImageContent
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    FOREIGN KEY (TagID) REFERENCES ContentTags
        ON DELETE CASCADE,
    PRIMARY KEY (imageId, TagID)
);

当前查询SQL（返回多行数据） #

SELECT img.*, c.tagname
FROM imagecontent img
JOIN imagetags i on img.id = i.imageid
JOIN contenttags c on c.id = i.tagid;

解决方案 #

利用PostgreSQL的STRING_AGG聚合函数可以将多个标签名合并为单个字符串，结合GROUP BY实现需求：

SELECT 
    img.Id,
    img.Title,
    img.Description,
    img.ImageUrl,
    img.CreationDate,
    STRING_AGG(c.TagName, ', ') AS Tags
FROM ImageContent img
LEFT JOIN ImageTags i ON img.Id = i.ImageId
LEFT JOIN ContentTags c ON c.Id = i.TagId
GROUP BY img.Id;

说明 #

1. STRING_AGG函数：将每个图片关联的所有标签名用指定分隔符（这里是, ）拼接成一个字符串，作为Tags列返回
2. LEFT JOIN：确保没有关联标签的图片也会被查询出来（如果只需要有标签的图片，可改为INNER JOIN）
3. GROUP BY img.Id：因为Id是ImageContent的主键，PostgreSQL允许在SELECT中包含该主键对应的其他非聚合字段，无需把所有字段都加到GROUP BY中

之前直接加GROUP BY报错的原因是：原查询中c.tagname既不是聚合函数也不在GROUP BY列表里，而使用STRING_AGG聚合标签后，就符合SQL的分组规则了。

内容的提问来源于stack exchange，提问作者GreatGaja