DEV Community

Mark
Mark

Posted on • Originally published at alchemist.camp

8

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

Image of Datadog

Create and maintain end-to-end frontend tests

Learn best practices on creating frontend tests, testing on-premise apps, integrating tests into your CI/CD pipeline, and using Datadog’s testing tunnel.

Download The Guide

Top comments (0)

Image of Datadog

The Essential Toolkit for Front-end Developers

Take a user-centric approach to front-end monitoring that evolves alongside increasingly complex frameworks and single-page applications.

Get The Kit

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay