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: …

@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…

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.

It seems not appropriate from my point of view.

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?

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

Can you show your table definition in raw SQL?

Postgres 11 > ``` status > CREATE TABLE > ``` > ``` status > INSERT 0 6 > ```

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

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.

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.

@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 n…

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

Questions & Help Questions

Ciboulette January 10, 2019, 2:45pm 7