1

I have three tables event, person and event_person_map

event:

id integer PRIMARY KEY
event_name text
event_date date

person:

id integer PRIMARY KEY
full_name text
email_address text

event_person_map:

id integer PRIMARY KEY
person_id integer [referencing person.id]
event_id integer [referencing event.id]

How can I strucutre my query to get an output which lists information about the event and the list of participants?

Example of the output would be something like this:

event_id| event_name | event_date |participants|
--------+------------+------------+------------+
 1      |   event1   |  1.1.2024  | json list  | 
 2      |   event2   |  1.2.2024  | json list  | 
 3      |   event3   |  1.3.2024  | json list  | 
 4      |   event4   |  1.4.2024  | json list  |

Structure of the json list of participants should look something like this:

[
   {
      "id":1,
      "full_name":"John Doe",
      "email_address":"[email protected]"
   },
   {
      "id":2,
      "full_name":"Jane Doe",
      "email_address":"[email protected]"
   }
]

I understand how to get list of participants separately but I am not sure what to do to make it be part of the query with event info.

The output does not have to be strictly like this, if there are different solutions that are more elegant I am all ears.

1
  • Please show your query so far. Have you tried joins? Commented Feb 25 at 19:15

2 Answers 2

1

If I were you, I would lose the JSON and use the PostgreSQL ARRAY type - PostgreSQL has very powerful array processing capabilities which might be better suited to your needs.

JSON is usually for "variable" data where the field names and values can vary (clothing items would be a good example), whereas your data is fairly "static" - i.e. the "person" table is fully defined at the beginning.

What I did to solve your issue was the following (all of the code below is available on the fiddle here):

  • CREATE our tables:

    • event

CREATE TABLE event
(
  id         INTEGER PRIMARY KEY,
  event_name TEXT NOT NULL,
  event_date DATE NOT NULL
);



  • person
CREATE TABLE person
(
  id            INTEGER PRIMARY KEY,
  full_name     TEXT NOT NULL UNIQUE,    -- make UNIQUE (suggestion)
  email_address TEXT
);



  • event_person
CREATE TABLE event_person
(
  person_id INTEGER NOT NULL REFERENCES person (id),
  event_id  INTEGER NOT NULL REFERENCES event  (id),
  PRIMARY KEY (person_id, event_id)   -- you can't attend the same event twice!
);

Then we populate our tables with a small amount of sample data:

INSERT INTO event VALUES
(1, 'concert',        '2024-01-10'),
(2, 'dinner',         '2024-01-24'),
(3, 'football match', '2024-02-05'),
(4, 'baseball game',  '2024-02-15');

and

INSERT INTO person VALUES
(1, 'Billy Joel', '[email protected]'),
(2, 'Bono',       '[email protected]'),
(3, 'Prince',     '[email protected]'),
(4, 'Lady Gaga',  '[email protected]');

and now, those who attended which events:

INSERT INTO event_person VALUES
(1,3), (2,4), (1,2), (3,4), (3,3), (2,2);

First, we run this query to obtain all of the data for all of the events - which events were attended by whom:

SELECT
  e.id AS eid, e.event_name AS en, e.event_date AS ed, 
  p.id AS pid, p.full_name AS pfn, p.email_address AS pea
FROM event e
JOIN event_person ep
  ON e.id = ep.event_id
JOIN person p
  ON p.id = ep.person_id
ORDER BY e.id;

Result:

eid  en              ed          pid  pfn         pea
  2  dinner          2024-01-24    1  Billy Joel  [email protected]
  2  dinner          2024-01-24    2  Bono        [email protected]
  3  football match  2024-02-05    1  Billy Joel  [email protected]
  3  football match  2024-02-05    3  Prince      [email protected]
  4  baseball game   2024-02-15    2  Bono        [email protected]
  4  baseball game   2024-02-15    3  Prince      [email protected]

But, that's not exactly the result you want - you want the events aggregated by attendee - so we use the ARRAY_AGG (examples) function as follows:

SELECT 
  eid, en, ed, ARRAY_AGG(pid::TEXT || ' ' || pfn || ' ' || pea)
FROM
(
  SELECT
  e.id AS eid, e.event_name AS en, e.event_date AS ed, 
  p.id AS pid, p.full_name AS pfn, p.email_address AS pea
  FROM event e
  JOIN event_person ep
    ON e.id = ep.event_id
  JOIN person p
    ON p.id = ep.person_id
  ORDER BY e.id
)
GROUP BY eid, en, ed
ORDER BY eid;

Result:

eid   en             ed           array_agg
2     dinner         2024-01-24   {"1 Billy Joel [email protected]","2 Bono [email protected]"}
3     football match 2024-02-05   {"1 Billy Joel [email protected]","3 Prince [email protected]"}
4     baseball game  2024-02-15   {"2 Bono [email protected]","3 Prince [email protected]"}

This is an alternative to the JSON approach, but is, I believe, better suited to the data model as presented. HTH and welcome to dba.se!

1

You can use a correlated subquery where you aggregate JSON from the other tables.

SELECT
  e.event_id,
  e.event_name,
  e.event_date,
  (
    SELECT
      jsonb_agg(to_jsonb(p.*))
    FROM event_person_map epm
    JOIN person p ON p.id = epm.person_id
    WHERE epm.event_id = e.id    -- correlation here
  ) AS participants
FROM event e;

An alternative, more classic but less flexible, option is to join everything together and do a giant GROUP BY.

SELECT
  e.event_id,
  e.event_name,
  e.event_date,
  jsonb_agg(to_jsonb(p.*)) AS participants
FROM event e
JOIN event_person_map epm ON epm.event_id = e.id
JOIN person p ON p.id = epm.person_id
GROUP BY
  e.event_id,
  e.event_name,
  e.event_date;

Side note on design:

  • Many-Many join tables do not need a separate id column. Their primary key should be a composite of the two columns such as PRIMARY KEY (person_id, event_id).
  • They also don't need to be suffixed _map, it should be obvious from the name itself what they are.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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