0

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?

1
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"}]}
3
  • 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. Sep 9 at 19:25
  • 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
    – Melkij
    Sep 9 at 19:43
  • 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 ... Sep 9 at 20:07
1

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:

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.