Querying nested JSONB fields with Ecto
Tags: elixir, json, ecto, postgres
Published: 2019-12-30 10:37:54

---
title: Querying nested JSONB fields with Ecto
published: true
description: Ecto has a map type, which is implemented in Postgres as a JSONB field. It's particularly useful for situations involving user-submitted data, and here's a tutorial about how to query within them...
tags: Elixir, JSON, Ecto, Postgres
canonical_url: https://alchemist.camp/articles/querying-nested-map-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:

```elixir
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:

```sql
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:

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

```sql
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:

```elixir
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:

```Elixir
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. 
```sql
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:

```Elixir
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](https://alchemist967856.typeform.com/to/FA0SUF)*