Ecto: Support JSON column type

[jasonwebster]

Not sure how this would play outside of the Postgres world, but that's just because I don't actively use any other databases.

This seems like it's accomplishable via a custom type. Big question is wether or not we're willing to pull in a dependency for a JSON parsing/serializing library, such as Poison. It may be possible to make that aspect of it pluggable.

I'm going to try implementing the custom type in an application and I'll let you know how it goes.

[josevalim]

Thanks. Please do ping us back!

[jasonwebster]

For my absolute minimum viable support, this worked just great:

defmodule App.Types.JSON do
  @behaviour Ecto.Type

  def type, do: :json

  def cast(any), do: {:ok, any}
  def load(value), do: Poison.decode(value)
  def dump(value), do: Poison.encode(value)
end

Especially since Poison implements the same response protocol as the type system expects, so simply passing along the tuples works well.

Going forward, would you be ok with a hard dependency, or would you prefer to make it pluggable? Given how straightforward this was, I'm not necessarily convinced and out of the box solution is even necessary.

[josevalim]

Given how straightforward this was, I'm not necessarily convinced and out of the box solution is even necessary.

This is what we have been advocating so far. Maybe you could write a blog post about it or some sort of article so at least developers have a reference? :)

[josevalim]

We can revisit about making this full part of Ecto once we support embedding (i.e. embedding a model in another one using jsonb). :) Thanks for pinging back!

[alanpeabody]

@josevalim Is there a plan for adding embedding support?

I was just thinking about starting to play with adding a embeds_many/embeds_one functionality that would serialize structs for my use case, probably doing a custom type as above for now. However I would be happy to work towards something more generic in ecto if it is on the roadmap.

[josevalim]

There are plans but not in the short-/medium-term roadmap.

[jclem]

Wondering if someone can help me with this: I have a custom type implemented as in @jasonwebster's example above, and a schema like this:

  schema "events" do
    field :type, :string
    field :data, Saturn.Types.JSON

    timestamps inserted_at: :created_at
  end

However, when posting JSON ({ "event": { "type": "data", "data": { "foo": "bar" } } }) to an endpoint like this:

  def create(conn, %{"event" => event_params}) do
    changeset = Event.changeset(%Event{}, event_params)

    if changeset.valid? do
      Repo.insert(changeset)
    end

    json conn, changeset
  end

I get this error:

** (exit) an exception was raised:
    ** (CaseClauseError) no case clause matching: %{"foo" => "bar"}
        (ecto) lib/ecto/changeset.ex:264: Ecto.Changeset.cast_field/3
        (ecto) lib/ecto/changeset.ex:240: Ecto.Changeset.process_param/6
        (elixir) lib/enum.ex:1036: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
        (elixir) lib/enum.ex:1036: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
        (ecto) lib/ecto/changeset.ex:226: Ecto.Changeset.cast/4
        (saturn) web/controllers/api/v1/event_controller.ex:13: Saturn.API.V1.EventController.create/2
        (saturn) web/controllers/api/v1/event_controller.ex:1: Saturn.API.V1.EventController.phoenix_controller_pipeline/2
        (saturn) lib/phoenix/router.ex:297: Saturn.Router.dispatch/2

[jasonwebster]

My original example above was (I'm editing it for clarity) initially wrong here. cast expects an {:ok, value} return value, just like everything else.

[jasonwebster]

I should also stress that the above isn't all that great. The type really should guard against things that can't possibly be cast to a serializable format at all, rather than leaving that to dump--since that's truly meant to turn whatever the model struct has into something your adapter can consume.

Give me a bit of time and I'll post a gist, or maybe even a hex package and repo of an opinionated Poison based JSON type def.

[jasonwebster]

FWIW, I've stopped using a generic JSON type in my apps and started enforcing what I'd expect to see in the attribute at the cast level. This means implementing types for application specific attributes, rather than just the generic JSON type. This works well because way more often than not you want whatever is in your JSON column to always either be an array or object, so writing up a type is super straightforward. Here's a more modern example pulled from one of my apps:

defmodule App.Types.References do
  @moduledoc """
  Custom Ecto type for Content References.

  Responsible for marshalling Reference structs to and from JSON for the
  database, and casting plain maps from input to structs.

  @see http://hexdocs.pm/ecto/Ecto.Type.html
  """
  @behaviour Ecto.Type

  alias App.Reference

  def type, do: :references

  @spec cast(list) :: {:ok, list} | :error
  def cast(list) when is_list(list) do
    Enum.reduce(list, {:ok, []}, fn
      _, :error -> :error
      ref, {:ok, acc} when is_list(acc) ->
        case Reference.cast(ref) do
          {:ok, ref} -> {:ok, acc ++ [ref]}
          {:error, _} -> :error
        end
    end)
  end
  def cast(_), do: :error

  def load(value) when is_binary(value) do
    case Poison.decode(value) do
      {:ok, list} when is_list(list) -> cast(list)
      {:error, _} -> :error
    end
  end
  def load(_), do: :error

  def dump(value) when is_list(value) do
    case Poison.encode(value) do
      {:ok, encoded} -> {:ok, encoded}
      {:error, _} -> :error
    end
  end
  def dump(_), do: :error
end

In this particular case, I'm ensuring that my type is:
1) Always a list
2) All elements of that list adhere to a custom specification

[jclem]

@jasonwebster Thanks, that should be super helpful. I eventually figured out the { :ok, any } issue, then had to take some time to realize that I needed to also still create and use a Postgrex JSON extension.

[jasonwebster]

Yeah. I've been using just a passthrough Postgrex extension that looks like this:

defmodule App.Postgrex.Extensions.JSON do
  alias Postgrex.TypeInfo

  @behaviour Postgrex.Extension

  def init(_parameters, _opts), do: nil
  def matching(_state), do: [type: "json"]
  def format(_state), do: :binary
  def encode(%TypeInfo{type: "json"}, data, _state, _library), do: data
  def decode(%TypeInfo{type: "json"}, json, _state, _library), do: json
end

That only works because I've done all the marshalling in the Ecto type. I'm a bit conflicted as to which is actually the correct place to do that. Disclaimer: all my examples are for a Postgres specific app, so I don't care about adapter agnosticism.

[jclem]

Same about agnosticism. That looks a little simpler, but yeah I'm not sure what the best approach is, yet.

[gjaldon]

I covered a slightly different approach here Using JSON Type in Ecto. Hope it helps!

[jasonwebster]

For people who land on this from searches:

I regret most of what I posted above (the initial comments about handling JSON encoding at the Ecto.Type level, not the type specific ones). This is simply much better served at the adapter level (especially for Postgres, considering there are two JSON types). Postgrex now just handles this by default https://github.com/ericmj/postgrex/blob/934f42812fa51aefa1904e8e70c212ff0e02e93c/lib/postgrex/extensions/json.ex and if you're not on that version, you can just dump that extension into your codebase.

[josevalim]

@jasonwebster if anyone lands on this. Ecto since v0.13.0 supports the :map type. It means JSON serialization out of the box as long as you also add Poison to your mix.exs file!