Upsert map in jsonb map array

So here is the code I came up with to generically generate an array param that will be stored on a jsonb property in ecto.

It only handles the root level, and is pretty limited, but I’m curious if the community has a better/more elegant/easier solution than I do. Let me re-phrase, I really believe you do, I just wanted to solicit advice.

Problem: jsonb array of objects stored on an Ecto model, these objects can be updated or added.

  @doc "if the id is in the map already, update it, otherwise append it"
  defp upsert_embed(%{id: id} = params, collection) when is_list(collection) do
   case Enum.find(collection, &(& == id)) do
     nil -> collection ++ [params]
     found -> updated = Map.merge(found, params)
              collection = collection -- [found] ## This is the piece I'm really not confident in.
              collection = collection ++ [updated]
  @doc "provided map doesn't have an id"
  defp upsert_embed(%{} = params, collection) when is_list(collection) do
    collection ++ [params]

  @doc """
  adds or updates a child object of an array of maps.
  def upsert_embedded_field(%__struct__{} = struct, key, %{} = candidate_params) do
    updated_embeds = Map.get(struct, key) || []
    updated_embeds = case length(updated_embeds) do
       0 ->
          upsert_embed(candidate_params, updated_embeds)
       _ ->
          updated_embeds = Enum.reduce(updated_embeds, updated_embeds,  fn(e, collection) ->
            upsert_embed(candidate_params, collection)
     # Return the mutated list

1 Like

A NoSQL DB is much better suited for something like this. MongoDB has support for atomic operations on embedded fields, and all sorts of atomic array operations as well.

Unfortunately Mongodb has serious security problems, and has had data retention issues in the past and honestly very slow for map reduce, search and math. I’ve deployed 6-9 apps on MongoDB and I like it for some things, but in my day job we have been urged to move all apps off of Mongo as soon as possible so when it comes to my hobby projects I am veering away from it now completely.

Postgres is vastly more feature-rich, time tested, performant, and out-performs Mongo for most NoSQL operations. I will switch frameworks before I switch the datastore. Languages come and go, but data storage is too important to make concessions on, and with Postgres there is no compromise, it just does exactly what I need it to do.

Now if only Ecto were as feature-rich :smiley:


NoSQLMongoDB though.

CouchDB is pure erlang. Also, Riak, Cassandra, RethinkDB and many more.


There are Postgres operators to do this directly on the jsonb column in an atomic way. You will need to use a fragment and play with the arguments a little bit.

The list of operators is in the postgresql docs. You are looking for jsonb_set in particular.

The Postgres adapter in my Flippant library has an example of doing upsert with an array on a jsonb column:

It is a bit complicated but works perfectly and is entirely atomic.


that is amazing thank you so much!

Is there robust ecto support for couchdb?

1 Like