Add and use a JSONB field in Phoenix and Ecto

PostgreSQL has native support for objects stored as JSON as actually binary JSON (or JSONB). With JSONB format, you can add an index to the column for faster and more efficient reads.

Even though the format supports both storing arrays of maps and nested maps, I suggest making it simple and just use an unnested single map.

The use case I am going for is to have some sort of user-entered product attributes that I later can search and filter on.

STEP 1 - GENERATE MIGRATIONS

I’m splitting this up in two migrations. One for adding the properties column on products and one for adding an index on that column.

It is however fine to do it in a single migration.

mix ecto.gen.migration add_properties_to_products
mix ecto.gen.migration add_index_to_products_properties

Open the first migration file and add:

# priv/repo/migrations/20200210061018_add_properties_to_products.exs

def change do
  alter table(:products) do
    add :properties, :map
  end
end

Note that I am defaulting to an empty map. That will let me avoid doing nil-checks later in the code.

In the second migration file I need to add both the up and down functionality. With GIN indexes, we can quickly query data using the JSON operators @>, ?, ?&, and ?|.

You will need to write the Postgres commands for creating and dropping the index by hand like this:

# priv/repo/migrations/20200210062352_add_index_to_products_properties.exs

def up do
  execute("CREATE INDEX products_properties ON products USING GIN(properties)")
end

def down do
  execute("DROP INDEX products_properties")
end

The last part here is to tell the Product that we now can use the new field and that it is of the type map. I also need to add the :properties in the changeset so I can write to it:

# lib/my_app/products/product.ex

schema "products" do
  ...
  field :properties, :map
  ...
end

@doc false
def changeset(product, attrs) do
  product
  |> cast(attrs, [:name, :description, :price, :properties])
  ...
end

These are the changes that are needed to get started with storing data in the properties column. Next I will show how that can be used.

STEP 2 - TEST IN IEX

Now it’s time to test this in the console. Open iex with:

iex -S mix

I want to add a new product with some extra properties:

# iex
alias MyApp.Products

Products.create_product(%{name: "Awesome Stout", description: "This stout is really awesome", price: 3.99, properties: %{color: "black", thickness: "Thick!"}})

And that should return a success struct:

I will also show the most simple case for querying the product with the correct property. And with Ecto, this can be done with fragment. I want to query for the product where color is black.

# iex
import Ecto.Query, warn: false
alias MyApp.Repo
alias MyApp.Products.Product

(from p in Product, where: fragment("? ->> ? = ?", p.properties, "color", "black"))
|> Repo.all()

That query should return the newly created product like:

With that example, you should now be able to start using this. One word of caution is to not go overboard and save data that is easier to have as regular columns in the database though.

But in a scenario where you want to store data that might come from user input or from an integration with another system, this might be a great fit.

OTHER QUERIES

Examples of other queries:

-- Checking for column existence
SELECT * FROM products WHERE properties ? 'thickness';

-- Checking for containment
SELECT * FROM products WHERE properties @> '{"color": "black"}';