How to treat json data from Postgres as a string in Ecto?

Phoenix Forum Questions / Help
, ,
1

I have a json field in postgres, but I’d like encoding/decoding to only happen on the client, so the data should just be treated as a string on the server. I’ve tried doing that with the following custom type:

defmodule Backend.Model.Types.VerbatimJson do
  @moduledoc """
  Stores a value in the db as json, but returns it as binary.
  Used for data where we don't need to deserialize it into a map on the server.
  """
  @behaviour Ecto.Type
  def type, do: :string

  # cast is called before dump. in our case we only expect binary strings.
  def cast(json_blob) when is_binary(json_blob) do
    {:ok, json_blob}
  end

  def cast(_), do: :error

  # load is called when fetching stuff *from* the database
  def load(json_blob) when is_binary(json_blob) do
    json_blob
  end

  # dump is called when ecto is saving stuff *to* the database.
  def dump(json_blob) when is_binary(json_blob) do
    {:ok, json_blob}
  end

  def dump(_), do: :error
end

The problem is that when using this type in a schema, and trying to save a value, it stores the whole value as a string, defeating the whole point of using the json value type. The only way I’ve gotten it to work is by returning a map from dump, but I’d like to avoid the whole string -> map conversion altogether and just treat the json string I’m passing as a literal json value.

(How) can I do that?

2

Do you actually make queries that involve the JSON? If not, it feels as if storing it as string in the Database should just work…

2 Likes
3

How did you make the table’s column? Show us the migration. Also, how did you declare the field in your Ecto schema?

4

We currently don’t make any queries for it, but given the minuscule overhead of validating the json, it’s nice to have the possibility there.

here’s the schema:

schema "theme_files" do
    field(:files, :string, default: <<"{}">>)
    timestamps()
    belongs_to(:apps, Backend.Model.App, foreign_key: :app_pk, references: :pk)
  end

migration

defmodule Backend.Repo.Migrations.AddNotificationsTable do
  use Ecto.Migration

  def change do
    create_if_not_exists table(:theme_files, primary_key: false) do
      add :pk, :uuid, primary_key: true
      add :app_pk, references(:apps, column: :pk, type: :uuid)
      add :files, :json

      timestamps()
    end
  end
end