PostgreSQL querying for array containing ids from a separate table

Question

I have a users table with this structure:

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

And a segments table with this structure:

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

I have built a query like this to find all the users which belong to segments:

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

However, I am not satisfied that using a subquery is the best way to accomplish this. Is there a cleaner way of writing this query, i.e. with a joins, or is a subquery the way to go?

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

If you really wanted to you could perform a join.

Fiddle

SELECT DISTINCT users.* 
FROM users 
JOIN segments 
  ON ( segments.id = ANY( users.segment_ids ) )
ORDER BY 1;

And if you really need to see what the segment_id that it matched:

SELECT DISTINCT users.*, array_agg(segments.id) "matched segment id"
FROM users 
JOIN segments 
  ON ( segments.id = ANY( users.segment_ids ) )
GROUP BY users.id, email, account_id, segment_ids
ORDER BY 1;

Insert your WHERE segments.name IN(...) wherever you see fit

Credit: @JamesFry for the DDL statements in the fiddle

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jan 24, 2018 at 2:27

vol7ron

42.3k22 gold badges126 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ryan Bigg Over a year ago

Thank you @vol7tron but I think this doesn't use a WHERE like I specified in my original question. How would I use a WHERE in your query?

James Fry · Accepted Answer · 2018-01-24 00:23:15Z

You could use the unnest function to do this:

For example, with the above tables and this data:

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

insert into users values (1, '[email protected]', 1, array[1,2]);
insert into users values (2, '[email protected]', 2, array[2,3]);

Running the following query:

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

yields:

1   "[email protected]"     1   1   "one"
1   "[email protected]"     1   2   "two"
2   "[email protected]"    2   2   "two"
2   "[email protected]"    2   3   "three"

Could I then add a WHERE on the end to match segment names, only returning the users that have the combination of segments specified?

Collectives™ on Stack Overflow

PostgreSQL querying for array containing ids from a separate table

2 Answers 2

Fiddle

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related