PostgreSQL 查询包含来自单独表的 id 的数组

Question

我有一个具有这种结构的 users 表:

CREATE TABLE users (
  id integer NOT NULL,
  email text,
  account_id integer,
  segment_ids integer[]
);

还有一个具有这种结构的segments表:

CREATE TABLE segments (
  id integer NOT NULL,
  name text
);

我构建了一个这样的查询来查找属于分割的所有用户:

SELECT * FROM users 
  WHERE segment_ids @> (
    SELECT ARRAY(
      SELECT id from segments 
        WHERE name IN ('Melbourne', 'Male', 'Team #1')
    )
  )

但是，我并不认为使用子查询是完成此任务的最佳方式。是否有一种更简洁的方式来编写此查询，即使用连接，或者子查询是可行的方法吗？

Answer 1

您可以使用 unnest执行此操作的功能:

例如，对于上面的表格和这个数据:

insert into segments values (1, 'one');
insert into segments values (2, 'two');
insert into segments values (3, 'three');

insert into users values (1, 'email@example.com', 1, array[1,2]);
insert into users values (2, 'email2@example.com', 2, array[2,3]);

运行以下查询:

select u.id, u.email, u.account_id, u.segment_id, s.name from 
(select u.id, u.email, u.account_id, unnest(u.segment_ids) segment_id from users u) as u 
right join segments s on s.id = u.segment_id

产量:

1   "email@example.com"     1   1   "one"
1   "email@example.com"     1   2   "two"
2   "email2@example.com"    2   2   "two"
2   "email2@example.com"    2   3   "three"