How to insert an empty list in a `jsonb` column?

Questions / Help
,
1

With this Ecto migration:

defmodule Migrations.Foo do
  use Ecto.Migration

  def change do
    create table(:foo, primary_key: false) do
      add(:id, :uuid, primary_key: true)
      add(:bars, :map, null: false)
    end
  end
end

I can’t get empty lists to be saved in the database.

This doesn’t work:

%Foo{bars: []} |> Repo.insert

# produces:
# ** (Postgrex.Error) ERROR 23502 (not_null_violation) null value in column "bars" violates not-null constraint

But this does work:

%Foo{bars: [%{foo: "bar"}]} |> Repo.insert

I tried to change my migration type to {:array, :map} but it doesn’t work either.

I can get around this by allowing null values and setting a default, like that add(:bars, :map, default: "[]") but it doesn’t seem right.

What am I missing?

2

Can you share with us how your schema looks like?

For me it is weird. I’ve just tried basing on your input with the latest Ecto 3.4.4

defmodule EmptyJson.Repo.Migrations.CreateUser do
  use Ecto.Migration

  def change do
    create table(:users) do
      add :jsonb_field, :jsonb, null: false
      add :json_field, :jsonb, null: false
      add :map_field, :map, null: false
    end
  end
end

The schema

defmodule EmptyJson.User do
  use Ecto.Schema

  schema "users" do
    field(:jsonb_field, {:array, :map})
    field(:json_field, {:array, :map})
    field(:map_field, {:array, :map})
  end
end

With the insert itself

%EmptyJson.User{id: 1, jsonb_field: [], json_field: [], map_field: []}
|> EmptyJson.Repo.insert()

And got the next log

INSERT INTO "users" ("id","json_field","jsonb_field","map_field") VALUES ($1,$2,$3,$4) [1, [], [], []]

If you print out the d\+ users in psql client then


                                                  Table "public.users"
   Column    |  Type  | Collation | Nullable |              Default              | Storage  | Stats target | Description 
-------------+--------+-----------+----------+-----------------------------------+----------+--------------+-------------
 id          | bigint |           | not null | nextval('users_id_seq'::regclass) | plain    |              | 
 jsonb_field | jsonb  |           | not null |                                   | extended |              | 
 json_field  | jsonb  |           | not null |                                   | extended |              | 
 map_field   | jsonb  |           | not null |                                   | extended |              |
3

You are right, it does work for this simple schema. :slightly_smiling_face:

In fact, it doesn’t work when the field is defined with embeds_many.

I created a repro here: https://github.com/dorian-marchal/repro-empty-list-jsonb/blob/master/README.md

Migration:

defmodule App.Repo.Migrations.Foo do
  use Ecto.Migration

  def change do
    create table(:foo) do
      add(:bars, :map, null: false)
    end
  end
end

Resulting table:

app_dev=# \d foo
                            Table "public.foo"
 Column |  Type  | Collation | Nullable |             Default
--------+--------+-----------+----------+---------------------------------
 id     | bigint |           | not null | nextval('foo_id_seq'::regclass)
 bars   | jsonb  |           | not null |

Schema:

defmodule App.Foo do
  use Ecto.Schema

  schema "foo" do
    embeds_many(:bars, __MODULE__.Bar, on_replace: :delete)
  end
end

defmodule App.Foo.Bar do
  use Ecto.Schema

  embedded_schema do
    field(:bar, :string)
  end
end

Result:

iex(3)> %App.Foo{bars: []} |> App.Repo.insert()
[debug] QUERY ERROR db=5.5ms queue=1.0ms idle=1938.1ms
INSERT INTO "foo" VALUES (DEFAULT) RETURNING "id" []
** (Postgrex.Error) ERROR 23502 (not_null_violation) null value in column "bars" violates not-null constraint

    table: foo
    column: bars

Failing row contains (1, null).

EDIT: I opened an issue on the ecto repo

4

Yeah, embedded_schema is not that mighty as always we expect :slight_smile: I believe that for now you could add a custom Ecto.Type wrapper to handle such case until v3.5 comes as Jose pointed in his comment.

5

I had this same question and here’s how I solved it (The solution wasn’t totally obvious to me from the conversation above):

# in your migration:
# You can also use type :map but :jsonb works too
add :my_jsonb_column, :jsonb, null: false, default: "[]"

# in your schema:
field :my_jsonb_column, {:array, :map}, default: []