SQLTeam.com | Weblogs | Forums

How to select many-to-many relations and pass it to json objects

I have these tables

events:
   id
   title
   content
   status
   creator = ForeignKey(users.c.id)
   location_id = ForeignKey(locations.c.id)
   activities_id  = ForeignKey(activities.c.id)
   ....
   
users:
   id
   ...
   
accounts:
   id
   user_id = ForeignKey(users.c.id)
   name
   surname
   
location:
   id
   city
   street
   
activities:
   id
   name
   
event_users
   id
   events_id = ForeignKey(events.c.id)
   users_id = ForeignKey(users.c.id)

what I'm trying to do

There is an activity (table events ). The event has a creator (table users ) associated with the accounts table. The event also has participants (table events_user ). I need to pull information on the event, data on its creator (name and surname) and data on the participants of the event (also name and surname) in one query. And I use the json object representation for easy further processing of the result

what I've got

SELECT
    events.id, events.title, events.creator, events.content, events.status,
    (json_agg(json_build_object('city', locations.city, 'street', locations.street)) -> 0)AS location,
    (json_agg(json_build_object('id', activities.id, 'name', activities.name)) -> 0) AS activity,
    (json_agg(json_build_object('name', accounts.name, 'surname', accounts.surname)) -> 0) AS creator
FROM
    events
JOIN
    locations ON events.location_id=locations.id
JOIN
    activities ON events.activities_id=activities.id
JOIN
    accounts ON events.creator=accounts.user_id
WHERE events.id = :pk GROUP BY events.id

result

{'id': 1, 'title': 'fffffff', 'creator': '{"name" : null, "surname" : null}', 'content': 'dddddddddddddd', 'status': 'OPEN', 'location': '{"city" : "Warsaw", "street" : "jezewskiego"}', 'activity': '{"id" : 1, "name" : "baskeball"}'}

But I can't figure out how I can add partisipate members, a many-to-many (link tables events_users and accounts ) here to get smth like

{'id': 1, 'title': 'fffffff', 'creator': '{"name" : Name, "surname" : Surname}', 'content': 'dddddddddddddd', 'status': 'OPEN', 'location': '{"city" : "Warsaw", "street" : "jezewskiego"}', 'activity': '{"id" : 1, "name" : "baskeball"}', 'members': ['{"id": 1, "name" : Name1, "surname" : Surname1}, {"id": 2, "name" : Name2, "surname" : Surname2}']}