Elixir ecto: check if postgresql map column has key

Question

To use json/jsonb data type ecto suggets to use fragments.

In my case, I've to use PostgreSQL ? operator to see if the map has such key, this however it will become something like:

where(events, [e], e.type == 1 and not fragment("???", e.qualifiers, "?", "2"))

but of course fragment reads the PostgreSQL ? as a placeholder. How can I check if the map has such key?

Answer 1

You need to escape the middle ? and pass a total of three arguments to fragment:

fragment("? \\? ?", e.qualifiers, "2")

Demo:

iex(1)> MyApp.Repo.insert! %MyApp.Food{name: "Foo", meta: %{price: 1}}
iex(2)> MyApp.Repo.insert! %MyApp.Food{name: "Foo", meta: %{}}
iex(3)> MyApp.Repo.all from(f in MyApp.Food, where: fragment("? \\? ?", f.meta, "price"))
[debug] SELECT f0."id", f0."name", f0."meta", f0."inserted_at", f0."updated_at" FROM "foods" AS f0 WHERE (f0."meta" ? 'price') [] OK query=8.0ms
[%MyApp.Food{__meta__: #Ecto.Schema.Metadata<:loaded>, id: 1,
  inserted_at: #Ecto.DateTime<2016-06-19T03:51:40Z>, meta: %{"price" => 1},
  name: "Foo", updated_at: #Ecto.DateTime<2016-06-19T03:51:40Z>}]
iex(4)> MyApp.Repo.all from(f in MyApp.Food, where: fragment("? \\? ?", f.meta, "a"))
[debug] SELECT f0."id", f0."name", f0."meta", f0."inserted_at", f0."updated_at" FROM "foods" AS f0 WHERE (f0."meta" ? 'a') [] OK query=0.8ms
[]

I'm not sure if this is documented anywhere, but I found the method from this test.