Querying nested JSONB fields with Ecto

Ecto has a map type, which is implemented in Postgres as a JSONB field. It's particularly useful for situations involving user-submitted data, where we don't know the exact shape the data will come in.

Consider this schema for user-submitted reviews:

defmodule MyApp.Repo.Migrations.CreateReview do
  use Ecto.Migration

  def change do
    create table(:reviews) do
      add :submitter_id, references(:users)
      add :item_id, references(:items)
      add :rating, :integer, default: 0, null: false
      add :metadata, :map

      timestamps()
    end

    create index(:reviews, [:submitter_id])
    create index(:reviews, [:item_id])
    create(unique_index(:reviews, [:submitter_id, :item_id]))

    # How do we create an index a JSONB sub-field?
  end
end

Querying into JSONB fields with raw SQL

Over time, we might come to find that some reviews include an item field in their metadata. These can be queried in the DB with:

select * from metadata
where metadata->'item' is not null;

Digging in further we could examine only reviews with an an item field in the metadata and another nested field price. To find all reviews with such a structure and a price of 35, we can do the following:

select * from metadata
where metadata @> '{"item": {"price": 35}}';

or

select * from metadata
where metadata->'item'->>'price' = '35'

Note: The difference between -> and ->> is that -> returns a JSON object and ->> returns the value as text!

Ecto Fragments

Ecto provides a handy escape hatch to use SQL for more specialized queries such as these. Assuming Ecto.Query has been imported, you can wrap the SQL you need in a fragment call. The following returns all the items within review metadata fields that have a price:

Repo.all(
  from r in "reviews",
  where: fragment("metadata->'item'->'price' is not null"),
  select: fragment("metadata->'item'")
)

Note: When using the @> JSON syntax, it's often convenient to use the ~s sigil with either | as delimiters and avoid a lot of backslash escaping. For example, using the JSON syntax to get a count of items with price 35 like this:

Repo.one(
  from e in "events",
  where: fragment(~s|metadata @> '{"item":{"price": 35}}'|),
  select: count(e.id)
)

Creating indexes on JSONB sub fields

In a large table where a particularly embedded field is important enough to query, such as price in this case, it's a good idea to add an index.

CREATE INDEX ON reviews((metadata->'item'->>'price'));

Inside Ecto migrations, we can accomplish this by wrapping the command in an execute(). Returning to the question at the top of this page, we can simply add the execute to the bottom of the change function in our migration:

create index(:reviews, [:submitter_id])
create index(:reviews, [:item_id])
create(unique_index(:reviews, [:submitter_id, :item_id]))

# How do we create an index on a JSONB sub-field?
execute("CREATE INDEX ON reviews((metadata->'item'->>'price'));")

Request a free email-based Elixir course from Alchemist.Camp