Ecto "left IN right" query using a fragment

Question

I would like to query a jsonb field using postgres IN operator (with Ecto library)

This code work with a simple = operator:

from a in query, where: fragment("?->>'format' = ?", a.properties, "foo")

But I cannot make any of these attempts to work:

from a in query, where: fragment("?->>'format' IN ?", a.properties, ["foo", "bar"])
from a in query, where: fragment("?->>'format' IN (?)", a.properties, ["foo", "bar"])
from a in query, where: fragment("?->>'format' IN ?", a.properties, "('foo', 'bar')"])

Any idea?

Answer 1

Besides Patrick's excellent response, keep in mind you can put only part of a query in a fragment too. For example, you can rewrite it to:

from a in query, where: fragment("?->>'format', a.properties) in ["foo", "bar"]

If you put the fragment in a macro, you can even get a readable syntax:

defmacro jsonb_get(left, right) do
  quote do
    fragment("?->>?", unquote(left), unquote(right))
  end
end

And now:

from a in query, where: jsonb_get(a.properties, "format") in ["foo", "bar"]