Elixir/Phoenix saving JSON to Postgres Database

Question

I have Googled plenty for ways to do this.

I used the phx.gen generator to generate some CRUD. I want to create repos in my database and store Github activity for it.

My migration:

def change do
  create table(:coderepos) do
    add :name, :string
    add :description, :string
    add :repo_name, :string
    add :activity_data_raw, :jsonb
    timestamps()
  end
end

My model:

schema "coderepos" do
  field :activity_data_raw, :map, default: %{}
  field :name, :string
  field :description, :string
  field :repo_name, :string
  timestamps()
end

Upon creating my new repo (via the generated HTML):

#Ecto.Changeset<
  action: :insert,
  changes: %{
    description: "Blah",
    name: "Blah",
    repo_name: "blah"
  },
  errors: [activity_data_raw: {"is invalid", [type: :map, validation: :cast]}],
  data: #App.Integrations.CodeRepo<>,
  valid?: false
>

I get the changeset above by doing an IO.inspect(changeset) in the :error response of my create function in my controller.

My create function:

def create(conn, %{"code_repo" => code_repo_params}) do
  case Integrations.create_code_repo(code_repo_params) do
    {:ok, code_repo} ->
      conn
      |> put_flash(:info, "Code repo created successfully.")
      |> redirect(to: code_repo_path(conn, :show, code_repo))
    {:error, %Ecto.Changeset{} = changeset} ->
      render(conn, "new.html", changeset: changeset)
      IO.inspect(changeset)
  end
end

This is the piece of JSON I submit in my form (along with name, description and repo name):

[{"week": 1532822400, "total": 6, "days": [0, 6, 0, 0, 0, 0, 0]}]

(Just adding that saving to my DB works fine with other things I do in my app, so there isn't any problems there).

Postgres 10.4, Elixir 1.6.5 / OTP 20, Phoenix 1.3.3, Phoenix Ecto 3.2

Any help would be greatly appreciated!

Are you passing that list as activity_data_raw? You've defined it to be a map, not list. — Dogbert
I looked at the primitive types listed here: hexdocs.pm/ecto/Ecto.Schema.html#content and based on the section below that, a map seemed to be the way to go to store JSON :| I did add Poison in my mix file and so on, I guess I just don't know where to go from here. — RuZ
Can you post the complete JSON value? If you want to store a list of maps, you'll need the type to be {:array, :map}. — Dogbert
The full JSON that gets returned from Github would look something like this: [{"week": 1501977600, "total": 0, "days": [0, 0, 0, 0, 0, 0, 0]}, {"week": 1502582400, "total": 0, "days": [0, 0, 0, 0, 0, 0, 0]}, {"week": 1503187200, "total": 0, "days": [0, 0, 0, 0, 0, 0, 0]}] I can't paste all of it because of character length, but it's just more of the same. I tried updating the type as you suggested, but it's still saying it's invalid. Ps: thanks for your time so far! It's appreciated — RuZ

RuZ RuZ · Accepted Answer · 2018-08-04T18:28:16

Thanks to @trevoke in the Elixir Slack, this is working now. You'll need to pass a map in (which is obvious I guess, though the other places I read made it sound like this is taken care of). Dogbert in the comments above was essentially saying the same thing.

In the create function:

raw = Map.get(code_repo_params, "activity_data_raw")
code_repo_params = Map.put(code_repo_params, "activity_data_raw", %{"data" => raw})

Elixir/Phoenix saving JSON to Postgres Database

1 Answers