Join with array - from sql to ecto?


i´ve a working query with:

select, u.lastname, array_agg(
from users as a
cross join unnest(tag_ids) as tag
join tags t on = tag
group by;

How would i put this with ecto?

So far i have found out i probably (not sure though) need something like:

fragment("unnest(?)", u.tag_ids) 
fragment("array_agg(DISTINCT ?)",

Problem: I don´t know how and where to put it.

Please help me.

1 Like

I am new to this too but I found a blog post where the ecto joins are being explained. I am not sure if the way described in the article is the best way or a best practice.

Here is the link

Hope it helps you.

Thanks, but this special case is not mentioned there.

Are you 100% sure you need CROSS JOIN? Because from what I see you need INNER JOIN tags t ON = ANY(tag_ids) and this is supported by Ecto via in/2.


@hauleth no :slight_smile:

thank you