Select many-to-many relations and pass it to json objects

user_9262339 0
Viewed 81

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

I have now been able to retrieve information except partiсipation user data

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" : Name, "surname" : SUrname}', 'content': 'dddddddddddddd', 'status': 'OPEN', 'location': '{"city" : "Warsaw", "street" : "jezewskiego"}', 'activity': '{"id" : 1, "name" : "baskeball"}'}

However, I can't figure out how I can add a many-to-many (link tables events_users and accounts as members) here to get something 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}']}

I'm using PostgreSQL 13.5 (Ubuntu 13.5-0ubuntu0.21.04.1)

postgresql sql
2 Answers

Lateral joins come in handy when dealing with JSON or XML.

I usually retrieve the data I need from the various tables in lateral queries. For example :

SELECT events.id, events.title, creatorlateral.creator, memberslateral.members
FROM events
INNER JOIN LATERAL (
  SELECT user_id, json_build_object('id', accounts.user_id, 'name', accounts.name, 'surname', accounts.surname)
  FROM accounts
) creatorlateral(user_id, creator) ON creatorlateral.user_id = events.creator
NATURAL JOIN LATERAL (
  SELECT events_id AS id, json_agg(json_build_object('id', accounts.user_id, 'name', accounts.name, 'surname', accounts.surname))
  FROM events_user
  INNER JOIN accounts ON accounts.user_id = events_user.users_id
  GROUP BY events_id
) memberslateral(id, members)

Note that you don't need json_agg when retrieving a single row.

Essentially:

  • We retrieve the creator in JSON format in the creatorlateral join, along with a user_id column to be able to join.
  • We retrieve the members in JSON format in the memberslateral join, for each events_id, along with the events_id column to be able to join. To ease things I used a NATURAL JOIN and renamed the events_id column to id, so that the join is made on the column id in each join side.

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=f3dd675aa2a8823cbed6e80aa5c9de47

user_5850800

You can try to use subquery on events_user and accounts tables then do JOIN and json_agg function.

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', a.name, 'surname', a.surname)) -> 0) AS creator,
    json_agg(json_build_object('id', a.user_id, 'name', a.name, 'surname', a.surname)) members
FROM
    events
INNER JOIN
    locations ON events.location_id=locations.id
INNER JOIN
    activities ON events.activities_id=activities.id
INNER JOIN (
  SELECT events_id AS id, 
          a.user_id,
          a.name,
          a.surname
  FROM events_user e
  INNER JOIN accounts a ON a.user_id = e.users_id
) a ON a.id = events.id
WHERE events.id = :pk 
GROUP BY events.id, events.title, events.creator, events.content, events.status

sqlfiddle

Edit

if your events_user table will be empty, your can try to use OUTER JOIN instead of INNER JOIN then use CASE WHEN to judgment events_user whether be empty.

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', a.name, 'surname', a.surname)) -> 0) AS creator,
    case when count(t1.user_id) = 0 then '[]' else json_agg(json_build_object('id', t1.user_id, 'name', t1.name, 'surname', t1.surname)) end  members
FROM
    events
INNER JOIN
    locations ON events.location_id=locations.id
INNER JOIN
    activities ON events.activities_id=activities.id
INNER JOIN 
    accounts a ON  events.creator=a.user_id
LEFT JOIN (
  SELECT events_id AS id, 
          a.user_id,
          a.name,
          a.surname
  FROM events_user e
  INNER JOIN accounts a ON a.user_id = e.users_id
) t1 ON t1.id = events.id
WHERE events.id = :pk 
GROUP BY events.id, events.title, events.creator, events.content, events.status

sqlfiddle

user_51760710
Related