Trying to query using where in with string array

Phoenix Forum Questions / Help
,
1

I’m trying to query an array of strings using where in but I’m getting this strange error, I’m doing something wrong in the query?

** (Plug.Conn.WrapperError) ** (Ecto.Query.CastError) lib/api/article/list_articles.ex:25: value `["test", "tech"]` in `where` cannot be cast to type {:in, {:array, :string}} in query:
     
     from a0 in Api.Articles.Article,
       where: a0.keywords in ^["test", "tech"],
       select: a0

my query

keywords = ["test", "tech"]

from(a in Article)
|> where([a], a.keywords in ^keywords)
|> Repo.all()

my schema

schema "articles" do
  field :keywords, {:array, :string}
end

migration

alter table(:articles) do
  add :keywords, {:array, :string}
end
1 Like
2

You want to check if array is element of array of strings. String is incompatible with array (in SQL).

2 Likes
3

I want to query all articles that keywords are contained in the array that I’m provide

1 Like
4

In SQL, one option would be to use the array overlap

SELECT * FROM articles WHERE ‘{“test”, “tech”}’ && keywords;

In an Ecto query, perhaps fragment() with interpolation might work. Something like

fragment("? && ?, ^keywords, [1])

  1. “test”, “tech” ↩︎

4 Likes