CODEWITHSUNDEEP
postgresql
Ryan Bigg
Ryan Bigg

Reputation: 107728

PostgreSQL querying for array containing ids from a separate table

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?

Upvotes: 0

Views: 2591

Answers (2)

vol7ron
vol7ron

Reputation: 42109

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

Upvotes: 1

James Fry
James Fry

Reputation: 1153

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"

Upvotes: 1

Related Questions