Insert String, Integer, Map, List in a jsonb PostgreSQL column

Questions / Help
,
1

Hi everybody,

I would like to know how can I insert data which can be a Map, String, List, Integer, Float in a jsonb PostgresSQL column ?

PostgreSQL is capable of doing such insertion in a jsonb field. I would like to find a way where Ecto (as an ORM) let me do this. Because currently my field is:

field(:data, :map) and it does not work when I try to insert: "toto" or 12 or [1,2]. And I have no other options, As I do not decide which type of data I’m receiving. Thanks :slight_smile:

2

You still have to wrap any data you want to persist in a map, like this:

%{string_field: "toto"}
%{integer_field: 12}
%{list_field: [1,2]}

Obviously, replace the field names with what you need.

3

It seems not appropriate from my point of view.

4

The DB column declaration says it’s a map, not any random piece of data you can throw at it though.

Can you describe your desired use case?

5

It’s an issue from Ecto ORM as it is possible to do it directly through PG method.

The type :json should exist in Ecto.Schema.Type

6

Can you show your table definition in raw SQL?

7

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=e3328babe5d590cadb6455ec9966ca20

8

Interesting. I would guess PostgreSQL automatically wraps things in maps below but I haven’t read up on it.

Thanks for the tidbit.

9

You can always create your own. The ecto type is a map type not a json one. In the end ecto is not bound to any db at all and it makes way more sense to have types, which align well to elixir than ones that align well with a certain database.

1 Like
10

I think wrapping into single key shouldn’t affect any business logic.

%{data: "toto"}
%{data: 12}
%{data: [1,2]}

Actually, to avoid struct.data.data just name it jsonb! so you retrieve it struct.data.jsonb … I think it makes sense.

11

@50kudos: ok, now perform jsonb search query on 500_000_000 rows in order to return 1_000_000 rows. Just few lettters, but they would be repeated way too often. It’s not really scalable option. Of course one thing like that would not force 100% server usage, but we should develop best solution and not only working solution.

12

Yeah, creating a type is a solution- something similar (but different) to this will likely work.

defmodule Type.Json do
  @behaviour Ecto.Type
  @moduledoc """
  To store values that might be any json value.

  Not intended as a general replacement for :map.
  """

  def type, do: :json

  def cast(value) when is_nil(value), do: {:ok, value}
  def cast(value) when is_boolean(value), do: {:ok, value}
  def cast(value) when is_binary(value), do: {:ok, value}
  def cast(value) when is_number(value), do: {:ok, value}
  def cast(value) when is_list(value), do: {:ok, value}
  def cast(value) when is_map(value), do: {:ok, value}
  def cast(_), do: :error

  def load(data), do: {:ok, data}

  def dump(value) when is_nil(value), do: {:ok, value}
  def dump(value) when is_boolean(value), do: {:ok, value}
  def dump(value) when is_binary(value), do: {:ok, value}
  def dump(value) when is_number(value), do: {:ok, value}
  def dump(value) when is_list(value), do: {:ok, value}
  def dump(value) when is_map(value), do: {:ok, value}
  def dump(_), do: :error
end
13

@mischov: Unfortunately it’s probably not enough. You would need to do all is_* checks (except is_list and is_map) on all List elements and Map. Notice that somebody could type map with for example Atom as value. In such way encoding could not work as expected.

14

Yeah, the above works for json input where that’s less of a concern, but for just arbitrary Elixir values you’d need to do more coercion.

15

@mischov: I think that this should work, but I did not fully tested it.

defmodule Type.Json do
  alias Ecto.Type

  @behaviour Ecto.Type
  @moduledoc """
  To store values that might be any json value.

  Not intended as a general replacement for :map.
  """

  def type, do: :json

  def cast(value) when is_nil(value), do: {:ok, value}
  def cast(value) when is_boolean(value), do: {:ok, value}
  def cast(value) when is_binary(value), do: {:ok, value}
  def cast(value) when is_number(value), do: {:ok, value}
  def cast(value) when is_list(value), do: Type.cast({:array, __MODULE__}, value)
  def cast(value) when is_map(value), do: Type.cast({:map, __MODULE__}, value)
  def cast(_), do: :error

  def load(data), do: {:ok, data}

  def dump(value) when is_nil(value), do: {:ok, value}
  def dump(value) when is_boolean(value), do: {:ok, value}
  def dump(value) when is_binary(value), do: {:ok, value}
  def dump(value) when is_number(value), do: {:ok, value}
  def dump(value) when is_list(value), do: Type.dump({:array, __MODULE__}, value)
  def dump(value) when is_map(value), do: Type.dump({:map, __MODULE__}, value)
  def dump(_), do: :error
end
16

I honestly think it’s not different on query (even without indexing) because it’s only slower on write (It will not re-parse once store). Of course its binary size could take more bytes per row (even if we name it just d

I agree it’s not scalable, though I’m not sure that’s in term of space or time complexity.

Lets not going into old debate of best performance vs best development time or time to market or best maintainability etc. :smile:

17

Look, I’m not senior developer which would describe it properly. I believe this resource says enough:

Generally we should think about it especially when others (like in above linked article) have do it and explained why. However I agree that it should not be a longer discussion - I just wanted to suggest better alternative solution.