Ecto queries on jsonb

Questions / Help
#1

Hi all, I have field :attributes of type :map in my documents table. Below is how I return a list of documents which have "public" => true in the :attributes field. I had to use raw SQL for this. There must be a better way!

The query:

defmodule Query do

  def public do
"""
    SELECT id FROM documents WHERE attributes @> '{"public": true}';
  """
end

ETC.

end

The search:

defmodule Search do
  def for_public do
    Ecto.Adapters.SQL.query!(Repo, Query.public).rows
    |> List.flatten
    |> Enum.map fn id -> Repo.get!(Document, id) end
  end
end
#2

You can use a fragment for accessing the JSONB field.

The Ecto documentation provides a few useful examples. I also have a library which uses fragments for querying JSONB fields, you can take a look at the relevant code here.

4 Likes
#3

Thankyou – took a quick look at your query_builder library – looks great and I will try it out.