Postgresql Removing duplicates in a strings in the row

Question

I have this code here

select ri.restaurant_id, string_agg(t.cuisine,',') as ncuisine
from restaurant_items ri 
join restaurants r on r.id = ri.restaurant_id 
left join food_taxonomy t on ri.name ilike concat('%',t.keywords,'%')
where ri.restaurant_id = 1
group by ri.restaurant_id

which gets me this results

restaurant_id  ncuisine 

1.               Italian,Italian,Italian,Italian,Italian,American,Italian,Italian,Italian,Italian,Italian,Italian,American,Italian,Italian,Italian,Italian,Italian,Italian,American,Italian,Italian,Italian,Italian,American,Italian,Italian,Italian,Italian,American,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Greek,Italian,Italian,Greek,Italian,Italian,Italian,Italian,Italian,American,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,French,Italian,Italian,American,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,Italian,American,Italian,Italian,Italian,Italian,Italian,American,French,American,Italian,Italian,American,American,Italian

is there a function that would go with my code and remove the duplicate strings in "ncuisine" so it only shows Italian, American and French?

I believe i can put something near the string_agg function but not sure what functions i can use.

Umut TEKİN · Accepted Answer · 2022-07-27 00:22:42Z

2

Distinct will make the difference:

select ri.restaurant_id, string_agg(distinct t.cuisine,',') as ncuisine
from restaurant_items ri 
join restaurants r on r.id = ri.restaurant_id 
left join food_taxonomy t on ri.name ilike concat('%',t.keywords,'%')
where ri.restaurant_id = 1
group by ri.restaurant_id;

edited Jul 27, 2022 at 0:22

answered Jul 27, 2022 at 0:15

Umut TEKİN

9722 gold badges11 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

Postgresql Removing duplicates in a strings in the row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related