Ecto query field where array contains a value

I have a field in my schema that is {:array, :string} and I want a query that returns the id if that field has an array that contains a specific term. I thought I figured it out based on:


but I can’t seem to create the appropriate Ecto query. Basically I want to translate

select * from mytable where search_term=ANY(search_field);

This is what I’m doing now and it compiles but not sure if it is going to actually do what I want.

from q in MyTable,
where: like(fragment("ARRAY_TO_STRING(?, ',')", q.search_field), ^search_term)


The ARRAY_TO_STRING query isn’t going to be able to use an index, so it’s going to be expensive if the table is large.

Does writing my_table.search_term == fragment("ANY(?)", my_table.search_field) in a where do what you’re looking for?

1 Like

Yes, I think that works better. Thank you!

Doesn’t in work?

from q in query, where: ^search_term in q.field

I just had a similar issue, yes this should work.