Ecto query Postgres JSON Array for a value

Question

I have the following schema, which is a JSONB field for a post table, which will save all tags used.

schema "posts" do
  ...
  field :tags, {:array, :string}
  ...
end

And it has an array of "tags" as "strings". I'd like to search for a string value inside of this array. I tried:

def search_by_tag(query, tag) do
  from p in query,
    where: fragment("? @> ?", p.tags, ^tag)
end

But without success, what I'm looking for is a way to search through the JSONB array and found a value if the values exists. Also it should keep the function for the query compatible with non JSONB queries to continue doing like:

Blog.Post |> Blog.Post.search_by_tag("tag1") |> Blog.User.active()

Answer 1

@> function expects the second operant to be array, so:

def search_by_tag(query, tag) do
  tags = [tag]
  from p in query,
    where: fragment("? @> ?", p.tags, ^tags)
end

Also ecto syntax support such cases by it self:

def search_by_tag(query, tag) do
  from p in query,
    where: tag in p.tags
end

Also for composable queries Blog.Post.search_by_tag("tag1") |> Blog.User.active(), you might consider to use "pipe-based syntax"