How can I return multiple columns from an array of ids?

Question

I have a two tables like the following:

workshops table
+----+--------------+------------+
| id |     name     | option_ids |
+----+--------------+------------+
|  1 | Conversation | {2,3}      |
+----+--------------+------------+
options table
+----+-----------+---------------+
| id |    day    |     time      |
+----+-----------+---------------+
|  1 | Monday    | 13:00 - 14:00 |
|  2 | Tuesday   | 12:00 - 13:00 |
|  3 | Wednesday | 11:00 - 12:00 |
+----+-----------+---------------+

I want to construct a Query where I retrieve the workshop name and also all the information for option_ids 2 and 3.

I would like to eventually construct a JSON like so:

{
    name: "Conversation",
    options: [
        { day: "Tuesday", time: "12:00 - 13:00" }, 
        { day: "Wednesday", time: "11:00 - 12:00" }
    ]
}

Looking at the postgresql.org documentation maybe it has something to do with the json_object function?

score 2 · Accepted Answer · answered Sep 09 '21 at 19:09

Few build-in functions like this query:

select json_build_object(
   'name', w.name, 
   'options', (
       select json_agg(json_build_object('day', o.day, 'time', o.time))       
       from options o 
       where o.id =any(w.option_ids)  -- I am assuming option_ids is an array, not string
   )
) as result 
from workshops w;

will build a json like this:

 {"name" : "Conversation", "options" : [{"day" : "Tuesday", "time" : "12:00 - 13:00"}, {"day" : "Wednesday", "time" : "11:00 - 12:00"}]}

Erwin Brandstetter · Answer 2 · 2021-09-09T20:26:42.767

is it possible to construct the 'options' property automatically by all column names?

Yes:

SELECT row_to_json(w) AS workshop
FROM  (
   SELECT w.name, (SELECT json_agg(o) FROM options o WHERE o.id = ANY(w.option_ids)) AS options
   FROM   workshops w
   ) w;

Notably, "all" means all columns from table options, including id.
To include only select columns like in your original question:

SELECT row_to_json(w) AS workshop
FROM  (
   SELECT w.name, (SELECT json_agg(o) FROM (
                      SELECT o.day, o.time FROM options o
                      WHERE  o.id = ANY(w.option_ids)
                      ) o
                  ) AS options
   FROM   workshops w
   ) w

db<>fiddle here

Both work for Postgres 9.3 or later. json_build_object() was added with Postgres 9.4. See:

Phill W. · Answer 3 · 2021-09-10T13:08:17.137

Question: Will you ever query this data by option_id[s]?

If the answer to this question might [ever] be "Yes", then your data structure is wrong.
Holding lists of values in a single field is generally accepted to be Bad Practice. Sure, there are functions that allow you to work with lists of values, but they will be slow compared to properly normalised data:

select * from workshops ;
+----+--------------+
| id |     name     |
+----+--------------+
|  1 | Conversation |
+----+--------------+
select * from options ;
+----+-----------+---------------+
| id |    day    |     time      |
+----+-----------+---------------+
| 11 | Monday    | 13:00 - 14:00 |
| 22 | Tuesday   | 12:00 - 13:00 |
| 33 | Wednesday | 11:00 - 12:00 |
+----+-----------+---------------+
select * from workshop_options ;
+-------------+-----------+
| workshop_id | option_id | 
+-------------+-----------+
|           1 |        22 | 
|           1 |        33 |
+-------------+-----------+

The data for constructing your JSON starts from here:

select 
  w.name
, o.day
, o.time 
from workshops w 
inner join workshop_options wo 
      on w.id = wo.workshop_id 
inner join options o 
      on wo.option_id = o.id 
order by 1, 2, 3 ;
+--------------+-----------+---------------+
| name         | day       | time          |
+--------------+-----------+---------------+
| Conversation | Tuesday   | 12:00 - 13:00 |
| Conversation | Wednesday | 11:00 - 12:00 |
+--------------+-----------+---------------+

How can I return multiple columns from an array of ids?

3 Answers3