Psst. It would be super cool if you could try the new Phoenix Boilerplate

Try now →

Tutorial

View on Github

Add an use a JSONB field in Phoenix and Ecto

ectojsonbphoenixpostgresql

PostgreSQL has native support for objects stored as JSON as actually binary JSON (or JSONB). With JSONB format, you can add index do 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 have some sort of userentered product attributes that I later can search and filter on.

STEP 1 - GENERATE MIGRATIONS

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

It 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, default: %{}
    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 Posgres commands for creating and droppin 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/tutorial/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 its 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 Tutorial.Products

Products.create_product %{name: "Aswesome 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 query for 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 Tutorial.Repo
alias Tutorial.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 its easier to have in as regular columns in the database though.

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

OTHER QUERIES

Examples on other queries:

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

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

Related Tutorials

Published 15 Feb

Create ghost loading cards in Phoenix LiveView

ectoliveviewphoenix

Unless you already didn't know, when a LieView component is mounted on a page, it runs the mount/2 function twice. One when the page is rendered fro..

Published 11 Feb

Add Tags with Ecto has_many, through in Phoenix - Tagging part 1

belongs_toectohas_manyphoenix

I want to add tags to products. And as usual there are a situation where a product can have many tags and a tag can belong to many products. Howeve..