clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2805490 fiddles created (40764 in the last week).

CREATE TABLE workshops (id int, name text, option_ids int[]); INSERT INTO workshops VALUES (1, 'Conversation', '{2,3}') ; CREATE TABLE options (id int, day text, time text); INSERT INTO options VALUES (1, 'Monday', '13:00 - 14:00') , (2, 'Tuesday', '12:00 - 13:00') , (3, 'Wednesday', '11:00 - 12:00') ;
1 rows affected
3 rows affected
 hidden batch(es)


-- include all columns from table options: 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;
workshop
{"name":"Conversation","options":[{"id":2,"day":"Tuesday","time":"12:00 - 13:00"}, {"id":3,"day":"Wednesday","time":"11:00 - 12:00"}]}
 hidden batch(es)


-- include select columns from table options: 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
workshop
{"name":"Conversation","options":[{"day":"Tuesday","time":"12:00 - 13:00"}, {"day":"Wednesday","time":"11:00 - 12:00"}]}
 hidden batch(es)