postgres STRING_AGG() returns duplicates?

Question

I have seen some similar posts, requesting advice for getting distinct results from the query. This can be solved with a subquery, but the column I am aggregating image_name is unique image_name VARCHAR(40) NOT NULL UNIQUE. I don't believe that should be necersarry.

This is the data in the spot_images table

spotdk=# select * from spot_images;
 id | user_id | spot_id |              image_name              
----+---------+---------+--------------------------------------
  1 |       1 |       1 | 81198013-e8f8-4baa-aece-6fbda15a0498
  2 |       1 |       1 | 21b78e4e-f2e4-4d66-961f-83e5c28d69c5
  3 |       1 |       1 | 59834585-8c49-4cdf-95e4-38c437acb3c1
  4 |       1 |       1 | 0a42c962-2445-4b3b-97a6-325d344fda4a
(4 rows)

SELECT Round(Avg(ratings.rating), 2) AS rating, 
       spots.*, 
       String_agg(spot_images.image_name, ',') AS imageNames
FROM   spots 
       FULL OUTER JOIN ratings 
                    ON ratings.spot_id = spots.id 
       INNER JOIN spot_images 
               ON spot_images.spot_id = spots.id 
WHERE  spots.id = 1 
GROUP  BY spots.id;

This is the result of the images row:

81198013-e8f8-4baa-aece-6fbda15a0498,
21b78e4e-f2e4-4d66-961f-83e5c28d69c5,
59834585-8c49-4cdf-95e4-38c437acb3c1,
0a42c962-2445-4b3b-97a6-325d344fda4a,
81198013-e8f8-4baa-aece-6fbda15a0498,
21b78e4e-f2e4-4d66-961f-83e5c28d69c5,
59834585-8c49-4cdf-95e4-38c437acb3c1,
0a42c962-2445-4b3b-97a6-325d344fda4a,
81198013-e8f8-4baa-aece-6fbda15a0498,
21b78e4e-f2e4-4d66-961f-83e5c28d69c5,
59834585-8c49-4cdf-95e4-38c437acb3c1,
0a42c962-2445-4b3b-97a6-325d344fda4a

Not with linebreaks, I added them for visibility.

What should I do to retrieve the image_name's one time each?

The duplicates are returned because of the FULL OUTER join. If you joined only spots and spot_images then you would not get duplicates. — forpas
– forpas, Commented Apr 12, 2020 at 15:00
@forpas do you just mean a standalone join with no outer/inner/left/right qualifier? — Jay J
– Jay J, Commented Jan 16, 2021 at 7:04

Gordon Linoff · Accepted Answer · 2020-04-12 14:55:16Z

6

If you don't want duplicates, use DISTINCT:

   String_agg(distinct spot_images.image_name, ',') AS imageNames

answered Apr 12, 2020 at 14:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

Jonas Grønbek Over a year ago

That was quick and easy - why is that required? Why does it aggregate over 11 records when there only exists 4?

Gordon Linoff Over a year ago

@JonasGrønbek . . . Aggregation functions aggregate over the entire set, including duplicate values. If you want distinct, you need to be explicit.

Jonas Grønbek Over a year ago

Why does the entire set have more than 4 image_names?

Gordon Linoff Over a year ago

@JonasGrønbek . . . There are either duplicates in your data or you don't have the right JOIN conditions.

GMB · Accepted Answer · 2020-04-12 14:57:30Z

Likely, there are several rows in ratings that match the given spot, and several rows in spot_images that match the given sport as well. As a results, rows are getting duplicated.

One option to avoid that is to aggregate in subqueries:

SELECT r.avg_raging
       s.*, 
       si.image_names
FROM   spots s
       FULL OUTER JOIN (
           SELECT spot_id, Round(Avg(ratings.rating), 2) avg_rating
           FROM ratings
           GROUP BY spot_id
       ) r ON r.spot_id = s.id
       INNER JOIN (
           SELECT spot_id, string_agg(spot_images.image_name, ',') image_names
           FROM spot_images
           GROUP BY spot_id
       ) si ON si.spot_id = s.id 
WHERE  s.id = 1

This actually could be more efficient that outer aggregation.

Note: it is hard to tell without seeing your data, but I am unsure that you really need a FULL JOIN here. A LEFT JOIN might actually be what you want.

Collectives™ on Stack Overflow

postgres STRING_AGG() returns duplicates?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related