Combine results into array in postgresql

Question

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?

1. please show us the sample data. Only the result is not really helpful. 2. It seems that you want to create a JSON object with a JSON array. Not a simple array. Is this right? — S-Man
– S-Man, Commented May 22, 2019 at 7:37

Rizwan · Accepted Answer · 2019-05-23 06:44:38Z

4

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;

edited May 23, 2019 at 6:44

answered May 22, 2019 at 7:51

Rizwan

2,44724 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gordon Linoff Over a year ago

. . Why are you converting the value to text? And have you heard of JOIN?

Rizwan Over a year ago

@GordonLinoff Thank you for feedback. Actually it does not needed. Updated answer.

TheWildHealer · Accepted Answer · 2019-05-22 07:39:46Z

0

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

answered May 22, 2019 at 7:39

TheWildHealer

1,6461 gold badge18 silver badges26 bronze badges

Comments

tomole · Accepted Answer · 2019-05-22 07:55:12Z

-1

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;

answered May 22, 2019 at 7:55

tomole

1,0063 gold badges15 silver badges37 bronze badges

Collectives™ on Stack Overflow

Combine results into array in postgresql

3 Answers 3

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Related