CODEWITHSUNDEEP
sqlarrayspostgresql
tomole
tomole

Reputation: 1006

Combine results into array in postgresql

I got the following postgresql tables:

Table "public.dates"
Column          |         Type            
----------------+-------------------------
id              | integer                       
start           | timestamp with time zone      
end             | timestamp with time zone     



Table "public.date_participants"
Column   |  Type           
---------+--------
date_id  | integer                  
user_id  | integer

I want to get a date with all its participants as an array. For example:

{ id: 252, start: xyz, end yzx, participants: [23, 51, 63, 67] }

So I created the following query:

SELECT
  dates.id,
  json_build_array(participants) as participants
FROM
  dates,
  (SELECT date_id, user_id FROM date_participants) as participants
WHERE dates.id=participants.date_id AND dates.ground_event_id = 252;

But this results in:

id  |          participants           
----+--------------------------------
252 | [{"date_id":252,"user_id":2}]
252 | [{"date_id":252,"user_id":191}]
252 | [{"date_id":252,"user_id":581}]
252 | [{"date_id":252,"user_id":582}]
(4 rows)

How to combine these rows into one row with all its user_ids as participants?

Upvotes: 2

Views: 2135

Answers (3)

Rizwan
Rizwan

Reputation: 2437

You can use array_agg(user_id) as part of your select statement to aggregate all your participant in one array. As this is the aggregation and the result has to be produced in one row you need to utilize the group by as well in your query

Something like :

SELECT
  d.id,d.start,d.end,
  array_agg(dp.user_id) as participants
FROM
  dates d, date_participants dp      
WHERE d.id=dp.date_id AND d.ground_event_id = 252
group by d.id,d.start,d.end;

Upvotes: 4

tomole
tomole

Reputation: 1006

I got a working query with help of aggregate:

SELECT
  dates.id,
  dates.start,
  dates.end,
  array_agg(date_participants.user_id) as participants 
FROM
  dates,
  date_participants
WHERE dates.id = date_participants.date_id AND dates.ground_event_id = 252
GROUP BY dates.id;

Upvotes: -1

TheWildHealer
TheWildHealer

Reputation: 1626

You can do such:

select
    dates.id,
    dates.start,
    dates.end,
    json_build_array(
        select user_id from date_participants where date_id = dates.id
    ) as participants
from
    dates

Upvotes: 0

Related Questions