Ecto + Elixir: How do I query a hashmap field?

Question

I have a model:

defmodule VideoChat.User do
  use VideoChat.Web, :model

  schema "users" do
    field :device_identifier, :string
    field :matches, :map

    timestamps()
  end

  ...
end

How would I look for all users with a "cool" key in their matches hash?

User |> where([u], u.matches["cool"] != nil) |> limit(1) |> VideoChat.Repo.one

Answer 1

:map fields are stored as JSONB fields in PostgreSQL by Ecto. Ecto does not provide any functions to do map specific operations on such fields, but it can be done using fragment and custom SQL.

The SQL query foo.bar ? 'baz' will check if the column bar of foo contains a value in the key "baz". This can be expressed with fragment like this:

fragment("? \\? ?", foo.bar, "baz")

So your code should be modified to:

User |> where([u], fragment("? \\? ?", u.matches, "cool")) |> limit(1) |> VideoChat.Repo.one

In a brand new Map model with key map of type :map:

iex(1)> Repo.insert! %MyApp.Map{map: %{}}
iex(2)> Repo.insert! %MyApp.Map{map: %{foo: 1}}
iex(3)> Repo.insert! %MyApp.Map{map: %{foo: 2}}
iex(4)> Repo.insert! %MyApp.Map{map: %{bar: 1}}
iex(5)> Repo.all MyApp.Map |> where([m], fragment("? \\? ?", m.map, "foo"))
[debug] QUERY OK source="maps" db=1.8ms decode=5.3ms
SELECT m0."id", m0."map", m0."inserted_at", m0."updated_at" FROM "maps" AS m0 WHERE (m0."map" ? 'foo') []
[%MyApp.Map{__meta__: #Ecto.Schema.Metadata<:loaded, "maps">, id: 2,
  inserted_at: #Ecto.DateTime<2016-12-07 10:19:53>, map: %{"foo" => 1},
  updated_at: #Ecto.DateTime<2016-12-07 10:19:53>},
 %MyApp.Map{__meta__: #Ecto.Schema.Metadata<:loaded, "maps">, id: 3,
  inserted_at: #Ecto.DateTime<2016-12-07 10:19:55>, map: %{"foo" => 2},
  updated_at: #Ecto.DateTime<2016-12-07 10:19:55>}]
iex(6)> Repo.all MyApp.Map |> where([m], fragment("? \\? ?", m.map, "bar"))
[debug] QUERY OK source="maps" db=2.9ms queue=0.2ms
SELECT m0."id", m0."map", m0."inserted_at", m0."updated_at" FROM "maps" AS m0 WHERE (m0."map" ? 'bar') []
[%MyApp.Map{__meta__: #Ecto.Schema.Metadata<:loaded, "maps">, id: 4,
  inserted_at: #Ecto.DateTime<2016-12-07 10:19:59>, map: %{"bar" => 1},
  updated_at: #Ecto.DateTime<2016-12-07 10:19:59>}]
iex(7)> Repo.all MyApp.Map |> where([m], fragment("? \\? ?", m.map, "baz"))
[debug] QUERY OK source="maps" db=2.2ms queue=0.1ms
SELECT m0."id", m0."map", m0."inserted_at", m0."updated_at" FROM "maps" AS m0 WHERE (m0."map" ? 'baz') []
[]