Mesclando informações de várias linhas em uma linha

Você pode usar uma subconsulta correlacionada onde agrega JSON de outras tabelas.

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;

Uma opção alternativa, mais clássica, mas menos flexível, é juntar tudo e fazer um gigante 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;

Nota lateral sobre design:

• As tabelas de junção muitos-muitos não precisam de uma idcoluna separada. Sua chave primária deve ser uma composição de duas colunas, como PRIMARY KEY (person_id, event_id).
• Eles também não precisam ter sufixo _map; deve ser óbvio pelo próprio nome o que são.

Se eu fosse você, perderia o JSON e usaria o tipo PostgreSQL ARRAY - o PostgreSQL possui recursos de processamento de array muito poderosos que podem ser mais adequados às suas necessidades.

JSON geralmente é para dados "variáveis" onde os nomes e valores dos campos podem variar (itens de roupas seriam um bom exemplo), enquanto seus dados são bastante "estáticos" - ou seja, a tabela "pessoa" está totalmente definida no início.

O que fiz para resolver seu problema foi o seguinte (todo o código abaixo está disponível no violino aqui ):

• CRIE nossas tabelas:

  • evento

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

• pessoa
  

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

• pessoa_evento
  

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!
);

Em seguida, preenchemos nossas tabelas com uma pequena quantidade de dados de amostra:

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');

e

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

e agora, quem participou de quais eventos:

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

Primeiro, executamos esta consulta para obter todos os dados de todos os eventos - quais eventos foram atendidos e quem:

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;

Resultado:

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]

Mas esse não é exatamente o resultado que você deseja - você deseja que os eventos sejam agregados por participante - então usamos a função ARRAY_AGG( exemplos ) da seguinte maneira:

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;

Resultado:

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]"}

Esta é uma alternativa à abordagem JSON, mas acredito que seja mais adequada ao modelo de dados apresentado. HTH e bem-vindo ao dba.se!