postgreSQL join and embed array of records

Question

I have two tables. One is events and the other is people. They look like this:

    events          | data_type 
--------------------+-----------
 event_id           | integer
 start              | TIMESTAMP WITH TIME ZONE
 end                | TIMESTAMP WITH TIME ZONE


 people          | data_type 
--------------------+-----------
people_id           | integer
name                | TEXT
event_id            | integer

I'd like to query a single event for event information, but I would also like to get a list of guests (tied to people) who are going to be at an event.

The output I am ultimately looking for looks like this (transformed to json):

{
  "event_id": 1,
  "start": Fri, 05 Aug 2016 21:18:38 GMT,
  "end": Fri, 05 Aug 2016 21:18:38 GMT,
  "guests": [
            {"name": "John", event_id: 1}, //record from people table
            {"name": "Sarah", event_id: 1} 
  ]
}

How can I elegantly perform a join in Postgresql that will retrieve all the associated people records I have?

So far I have the following query:

SELECT
*
FROM events, people
WHERE events.event_id = people.event_id 

Answer 1

Not sure If I mocked up your tables right. Does this do the trick?

with events as (select * from events)
  select jsonb_set(
    to_jsonb(events.*)
    , '{guests}'
    ,to_jsonb(array_agg(to_json(people))
  )
)
from people
join events on people.event_id = events.event_id
group by events.*
;