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?

• Anthony Genovese: What have you tried so far?

Answer 1

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"}]}

• Gonzalo Rocha: Nice! Just what I needed! Further question: is it possible to construct the 'options' property automatically by all column names? Instead of json_buid_object('day', o.day) could I just supply the row and construct all the property names by column? I have like seven columns that I want to include in that property but I would prefer not to write every single column manually.
• Melkij: We have to_json and row_to_json functions. If you need all columns from options table - then select array_agg(row_to_json(o)) from options o
• Erwin Brandstetter: Can be simplified to SELECT json_agg(o) FROM options s. Returns a JSON array as type json as opposed to json[]. Either works for the task ...

Answer 2

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:

• Select columns inside json_agg
• Postgres multiple columns to json

Answer 3

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 |
+--------------+-----------+---------------+