PostgreSQL 中匹配逗号分隔的ID字符串， 使用 array，string_to_array，any

PostgreSQL 中匹配逗号分隔的ID字符串， 使用 array，string_to_array，any

场景：两张表，books 和 tags 表，一个 book 对应多个 tag。但是 book 把 tag 信息存在一个字符串中，用逗号分隔。

如何匹配这种数据

代码演示

-- 建表
create table books(
    id varchar(32),
    name varchar(256),
    tags varchar(256)
);


create table tags(
    id varchar(32),
    name varchar(256)
);


-- 测试数据
INSERT INTO tags VALUES('01', 'Tag01');
INSERT INTO tags VALUES('02', 'Tag02');
INSERT INTO tags VALUES('03', 'Tag03');
INSERT INTO tags VALUES('04', 'Tag04');

insert into books VALUES('01', 'Book01', '01');
insert into books VALUES('02', 'Book02', '01,02');
insert into books VALUES('03', 'Book03', '01,02,03');



-- 查询1
-- 找到任一匹配的标签
-- STRING_TO_ARRAY 字符串--> Array
-- ANY(Array[]) 满足 Array 的任一元素。 注意：ANY 必须放在表达式的右侧。
SELECT
    * 
FROM
    books b
    LEFT JOIN tags T ON T."id" = ANY ( STRING_TO_ARRAY( b.tags, ',' ) )

-- 结果
-- 可以看到结果，每本书有几个标签就有几条数据。
-- 但这可能不是我们希望的结果形式
-- 01  Book01  01        01  Tag01
-- 02  Book02  01,02     01  Tag01
-- 02  Book02  01,02     02  Tag02
-- 03  Book03  01,02,03  01  Tag01
-- 03  Book03  01,02,03  02  Tag02
-- 03  Book03  01,02,03  03  Tag03

-- 查询2
-- 按照书分组，使用 array_agg 处理标签
SELECT
    b.ID,
    b.NAME,
    array_agg(t.name)
FROM
    books b
    LEFT JOIN tags t ON t."id" = ANY ( STRING_TO_ARRAY( b.tags, ',' ) ) 
GROUP BY
    b.ID,
    b.NAME 
ORDER BY
    b.ID

-- 结果
-- 01  Book01  {Tag01}
-- 02  Book02  {Tag01,Tag02}
-- 03  Book03  {Tag01,Tag02,Tag03}