Trying to query using where in with string array

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}


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

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


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

1 Like

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, ^[“test”, “tech”])