Merging information from multiple rows into one row

Question

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":"john@doe.com"
   },
   {
      "id":2,
      "full_name":"Jane Doe",
      "email_address":"jane@doe.com"
   }
]

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.

score 2 · Accepted Answer · answered Feb 26 '24 at 04:41

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', 'billy@example.net'),
(2, 'Bono',       'bono@eanet.ie'),
(3, 'Prince',     'prince@heaven.com'),
(4, 'Lady Gaga',  'lg@disney.com');

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  billy@example.net
  2  dinner          2024-01-24    2  Bono        bono@eanet.ie
  3  football match  2024-02-05    1  Billy Joel  billy@example.net
  3  football match  2024-02-05    3  Prince      prince@heaven.com
  4  baseball game   2024-02-15    2  Bono        bono@eanet.ie
  4  baseball game   2024-02-15    3  Prince      prince@heaven.com

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 billy@example.net","2 Bono bono@eanet.ie"}
3     football match 2024-02-05   {"1 Billy Joel billy@example.net","3 Prince prince@heaven.com"}
4     baseball game  2024-02-15   {"2 Bono bono@eanet.ie","3 Prince prince@heaven.com"}

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!

score 1 · Answer 2 · answered Feb 26 '24 at 00:28

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.

Merging information from multiple rows into one row

2 Answers2