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?

One (ugly) thing that seems to work is to generate the list of terms beforehand, and then putting them in the query: `list = Enum.join(["foo", "bar"], ","); from a in query, where: fragment("?->>'format' IN (?)", a.properties, ^list])` — pjam, Jan 08 '16 at 03:06

score 10 · Accepted Answer · answered Jan 08 '16 at 09:57

10

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"]

answered Jan 08 '16 at 09:57

José Valim

50,409
12
130
115

Oh, very nice! Gotta go refactor some queries ;-) – Patrick Oscity Jan 08 '16 at 10:01
That's great, thanks! Ecto query is powerful but sometimes it seems a bit too magic for me :) – Chris Jan 08 '16 at 10:53
Very cool! Didn't realize you could embed fragments like that. – Venkat D. Oct 18 '16 at 22:33

score 8 · Answer 2 · answered Jan 08 '16 at 09:53

This has nothing to do with JSONB in particular. Ecto will turn your list of types into a Postgres ARRAY, which does not work with the IN operator:

psql> SELECT 1 IN(ARRAY[1, 2, 3]);
ERROR:  operator does not exist: integer = integer[]

However you can use = ANY() to check if the value is contained in an ARRAY:

psql> SELECT 1 = ANY(ARRAY[1, 2, 3]);
 ?column?
----------
 t
(1 row)

You should be able to use the following fragment to achieve the same with Ecto:

fragment("?->>'format' = ANY(?)", u.properties, ["foo", "bar"])

Indeed, I could see in log messages that my list was converted in a Postgres array, but I couldn't figure out what to do. Thanks! — Chris, Jan 08 '16 at 10:51

Ecto "left IN right" query using a fragment

2 Answers2

Linked