How to convert several duplicate rows into an array in SQL (Postgres)?

Question

I have the following table One:

 id │ value 
────┼───────
  1 │ a
  2 │ b

And Two:

  id │ value 
─────┼───────
  10 │ a
  20 │ a
  30 │ b
  40 │ a
  50 │ b

One.value has a unique constraint but not Two.value (one-to-many relationship).

Which SQL (Postgres) query will retrieve as array the ids of Two whose value match One.value? The result I am looking for is:

          id │ value 
─────────────┼───────
  {10,20,40} │ a
     {30,50} │ b

Answer 1

Check on SQL Fiddle

SELECT array_agg(id) AS id, "value"
  FROM Two
 GROUP BY "value";

Using value as identifier (column name here) is a bad practice, as it is a reserved keyword.