如何基于my_schema.user的tag_id查询conversation表记录？

数据库结构 #

CREATE SCHEMA IF NOT EXISTS my_schema;

CREATE TABLE IF NOT EXISTS my_schema.user (
    id SERIAL PRIMARY KEY,
    tag_id BIGINT NOT NULL
);

CREATE TABLE IF NOT EXISTS my_schema.conversation (
    id SERIAL PRIMARY KEY,
    user_ids BIGINT[] NOT NULL
);

测试数据 #

INSERT INTO my_schema.user VALUES
    (1, 55555),
    (2, 77777);

INSERT INTO my_schema.conversation VALUES
    (1, '{1,2}');

原查询逻辑 #

已知my_schema.user的id值时，可通过以下语句查询关联的会话记录：

SELECT *
FROM my_schema.conversation
WHERE user_ids @> '{1}'

需求 #

需要改用my_schema.user的tag_id替代id来实现上述查询效果。

解决方案 #

方法一：子查询获取对应ID后匹配 #

先通过tag_id找到对应的用户ID，再用数组包含操作符查询会话：

SELECT c.*
FROM my_schema.conversation c
WHERE c.user_ids @> ARRAY(SELECT u.id FROM my_schema.user u WHERE u.tag_id = 55555);

方法二：JOIN关联查询 #

通过JOIN关联用户表，筛选出包含目标tag_id对应用户的会话（需加DISTINCT避免重复结果）：

SELECT DISTINCT c.*
FROM my_schema.conversation c
JOIN my_schema.user u ON c.user_ids @> ARRAY[u.id]
WHERE u.tag_id = 55555;

方法三：多tag_id匹配场景 #

如果需要匹配多个tag_id对应的用户，可先聚合这些用户的ID数组再进行匹配：

SELECT c.*
FROM my_schema.conversation c
WHERE c.user_ids @> (SELECT ARRAY_AGG(u.id) FROM my_schema.user u WHERE u.tag_id IN (55555, 77777));

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