Perform multiple updates (upserts) on Embedded documents

[Jagadeesh_Koka]

My collection is as below;

    {
       "_id" : "1",
        "firstName" : "paul",
        "dob" : "2012-12-12",
        "friends" [ {
                      "_id" : "friend_1",
                      "firstName" : "Karan"
                    },
                    {
                      "_id" : "friend_2",
                      "firstName" : "Chasay"
                    } 
                  ]
    }

Now I want to perform upsert on both nested, and main document
Input Json as below

    {  
       "_id" : "1", 
       "lastName" : "Cary", 
        "firstName" : "paul",
        "dob" : "2012-12-12", 
       "friends" : [ 
                     {
                       "_id" : "friend_1",
                       "lastName" : "Eric"
                      },
                     {
                       "_id" : "friend_3",
                       "lastName" : "Charan"
                      }  
                      ] 
       }

Output should be

    {  
       "_id" : "1", 
       "lastName" : "Cary", 
       "friends" : [ 
                     {
                       "_id" : "friend_1",
                       "lastName" : "Eric",
                       "firstName" : "Karan"
                      },
                     {
                       "_id" : "friend_3",
                       "lastName" : "Charan"
                      } ,
                     {
                      "_id" : "friend_2",
                      "firstName" : "Chasay"
                    }  
                      ] 
       }

The thing is this should be done in one query, if not possible please suggest,

What I have tried:

    Query q  =new Query().addCriteria(Cirtiera.where("_id").is(person.getId()));
    Document doc = new Document();
    template.getConverter().write(person, doc);
    Update update = Update.fromDocument("$set", doc);
    template.upsert(q, update, Person.class);

The above query upserts the main object, but replaces whole friends list,
I want to use Criteria.where("_id").is(person.getId()).and("friends.id").is(person.getFriendsList().getId())
this query to make upsert in embedded array.

how can I achieve that ?

I have also posted this question in Stackoverflow: mongodb - spring data mongo upsert on embedded array of objects and main document - Stack Overflow

In summary, what I want to do is to perform upsert operations in various levels of document, like adding new item to embedded array, adding new field or changing to new value in existing field and performing $set operation on main document.

[Takis]

Hello and welcome : )

There is $push update operator that adds a member into an array.

Besides those update operators,we have pipeline updates,that can use the aggregate operators and can do all shorts of complicated updates if needed.

Check out the simple $push operator to add into an array and the $set to update a field,and if you still have problems if you can give the data in initial form and how you want them to be after the update.

[Jagadeesh_Koka]

Hi @Takis, You can see in the question, How the initail json looks like and output json, should look like

[Takis]

Hello : )

You can update it with a query like bellow.
(the bellow update uses pipeline update works for mongodb >=4.2)

Data before update

Query
remove firstname,dod,friend_1 add firstname karan,push {"_id" : “friend_2”,“firstName” :“Chasay”}

{
  "update": "testcoll",
  "updates": [
    {
       "q": {
        "_id": "1"
      },
      "u": [
        {
          "$addFields": {
            "friends": {
              "$concatArrays": [
                {
                  "$map": {
                    "input": "$friends",
                    "as": "friend",
                    "in": {
                      "$cond": [
                        {
                          "$eq": [
                            "$$friend._id",
                            "friend_1"
                          ]
                        },
                        {
                          "$mergeObjects": [
                            "$$friend",
                            {
                              "firstName": "Karan"
                            }
                          ]
                        },
                        "$$friend"
                      ]
                    }
                  }
                },
                [
                  {
                    "_id": "friend_2",
                    "firstName": "Chasay"
                  }
                ]
              ]
            }
          }
        },
        {
          "$unset": [
            "firstName",
            "dob"
          ]
        }
      ],
      "multi": true
    }
  ]
}

Data after the update

To run it with your driver take the q = filter, and the u = the pipeline update.

The above doesn’t do upsert,if the document isn’t found nothing happens.
The bellow does the same as above but upsert also.
inserts a document {"_id" “10”,“friends” []} we didnt found _id 10 so we add this document.

{
  "update": "testcoll",
  "updates": [
    {
      "q": {
        "_id": "10"
      },
      "u": [
        {
          "$addFields": {
            "friends": {
              "$cond": [
                {
                  "$ne": [
                    {
                      "$type": "$friends"
                    },
                    "missing"
                  ]
                },
                {
                  "$concatArrays": [
                    {
                      "$map": {
                        "input": "$friends",
                        "as": "friend",
                        "in": {
                          "$cond": [
                            {
                              "$eq": [
                                "$$friend._id",
                                "friend_1"
                              ]
                            },
                            {
                              "$mergeObjects": [
                                "$$friend",
                                {
                                  "firstName": "Karan"
                                }
                              ]
                            },
                            "$$friend"
                          ]
                        }
                      }
                    },
                    [
                      {
                        "_id": "friend_2",
                        "firstName": "Chasay"
                      }
                    ]
                  ]
                },
                []
              ]
            }
          }
        },
        {
          "$unset": [
            "firstName",
            "dob"
          ]
        }
      ],
      "upsert": true,
      "multi": true
    }
  ]
}

It inserted

[Jagadeesh_Koka]

Thank you very much Takis, I will try to use this and let you know the outcome,
And if those fields comes, dynamically, I mean we don’t know which is new field or old field in that case will this help ?

[Takis]

you can wrap the query inside a function,and use the dynamic arguments,if this is what you ask.
we can use update operators also to do the updates and not pipeline updates,but here pipeline is used,pipeline ones are more powerful but takes some time to learn the pipelines operators.

[Jagadeesh_Koka]

HI Takis, is this query is for MongoShell ? can I have full syntax of the query, I get syntatical errors when running in MongoShell.

[Takis]

This is a update command,to run with runCommand.
To run it with mongoshell method you can use for example this methods

db.collection.updateOne(filter, update, options)
db.collection.updateOne(filter, update)

filter = q in the above query
update = u in the above query

So you will have sometging like (with your db and collection)

db.collection.updateOne(
{
  "_id": "10"
},
[
        {
          "$addFields": {
            "friends": {
              "$cond": [
                {
                  "$ne": [
                    {
                      "$type": "$friends"
                    },
                    "missing"
                  ]
                },
                {
                  "$concatArrays": [
                    {
                      "$map": {
                        "input": "$friends",
                        "as": "friend",
                        "in": {
                          "$cond": [
                            {
                              "$eq": [
                                "$$friend._id",
                                "friend_1"
                              ]
                            },
                            {
                              "$mergeObjects": [
                                "$$friend",
                                {
                                  "firstName": "Karan"
                                }
                              ]
                            },
                            "$$friend"
                          ]
                        }
                      }
                    },
                    [
                      {
                        "_id": "friend_2",
                        "firstName": "Chasay"
                      }
                    ]
                  ]
                },
                []
              ]
            }
          }
        },
        {
          "$unset": [
            "firstName",
            "dob"
          ]
        }
      ])

[Jagadeesh_Koka]

Takis,

Thank you very much for your support, I am trying to implement this using Spring Data MongoDB, somehow not able to produce this one,

to confirm again, I would be receiving friends list (this can have new elements or old elements with various fields changes or new fields for a list item ), so I need to loop through incoming friends list and match if exists then I have to make update or else insert that element using addField can all this has to be done in single query
is it possible ?
if not, please suggest guidelines.

[Takis]

Yes i know spring data query builder is different that Mongo Query Language
I dont use it, so i cant translate that in Spring data.
Maybe someone else can help on this,or make a new query from start.

Mongodb has updates that accept pipelines,so you can do complicated updates,
for example if you have have 10 friends to add or update,it can all be done in 1 query.
But you can do it in 1 query/friend also.

See $reduce,$map,$filter aggregate operators they can help you with arrays.

[Jagadeesh_Koka]

@Takis,

When I ran the query, its adding duplicate elements in friends array if we pass the same element twice.

Below is what query supposed to do.
if(friends._id eq 'incomeing array friends Id') { //do $set operatinos } else{ // do $push operation. }
And also in $addField you have added $cond to check the $type does that means it will check whether friends array exists or not in give document ?

[Jagadeesh_Koka]

@Takis

I have tried below query, but its not doing update

db.person.update({ personId: "4e497ed5-095a-4ab9-ab00-3953939d20a4" },
[
    {
        $map: {
            "input": "$friends", "as": "friend", "in": {
                $cond: [
				
				{ $eq: { "$$friend.friendId": "7" } },

                { $set: { "firstName": "trail1", "role": "Voter-1" } }, 
				{
                    $push: {
                        "friends": {
                            "friendId": "7", "firstName": "insertOne"
                        }
                    }
                }]
            }
        }
    },
    {
        $set : {"dob" : {$date : "2012-7-1"}}
    }
])

[Takis]

Hello : )

The above query was not just a array update,it was to change the data the way you said you want them.In the last query you sended you use $push operator,inside an aggregation and you
cant do that.You cant mix aggregation operators/query operators/update operators.

Exception is that you can use aggregate operators to query with $expr.

You can do array update.The bellow does

1. filter to check if friend already existed based on frined._id(if filter result is empty=> its new friend)
2. if not existed(not update) adds in the end of the array
3. if it existed, with $map,$mergeObjects the old friend data with the new friend data
   (no need to do merge you can do replace,or any operation you want)

Data in

{
  "_id": "1",
  "lastName": "Cary",
  "firstName": "paul",
  "dob": "2012-12-12",
  "friends": [
    {
      "_id": "friend_1",
      "lastName": "Eric"
    },
    {
      "_id": "friend_3",
      "lastName": "Charan"
    }
  ]
}

New friend (new friend will be like a variable in your driver)

{"_id" : "friend_1", "lastName" : "Don"}   //this will do update
OR
{"_id" : "friend_10", "lastName" : "Don"}  //this will do add in the end of array

Query(its a command, u= the filter,u= the update(here its pipeline update))
The query is written with newFriend={"_id" : “friend_1”, “lastName” : “Don”}
In the bellow query replace it with the newFriend variable,also where you
see “friend_1” replace it with newFriend.get("_id")

{
  "update": "testcoll",
  "updates": [
    {
      "q": {
        "$expr": {
          "$eq": [
            "$_id",
            "1"
          ]
        }
      },
      "u": [
        {
          "$addFields": {
            "friends": {
              "$let": {
                "vars": {
                  "isNew": {
                    "$eq": [
                      {
                        "$size": {
                          "$filter": {
                            "input": "$friends",
                            "as": "friend",
                            "cond": {
                              "$eq": [
                                "$$friend._id",
                                "friend_1"
                              ]
                            }
                          }
                        }
                      },
                      0
                    ]
                  }
                },
                "in": {
                  "$cond": [
                    "$$isNew",
                    {
                      "$concatArrays": [
                        "$friends",
                        [
                          {
                            "_id": "friend_1",
                            "lastName": "Don"
                          }
                        ]
                      ]
                    },
                    {
                      "$map": {
                        "input": "$friends",
                        "as": "friend",
                        "in": {
                          "$cond": [
                            {
                              "$eq": [
                                "$$friend._id",
                                "friend_1"
                              ]
                            },
                            {
                              "$mergeObjects": [
                                "$$friend",
                                {
                                  "_id": "friend_1",
                                  "lastName": "Don"
                                }
                              ]
                            },
                            "$$friend"
                          ]
                        }
                      }
                    }
                  ]
                }
              }
            }
          }
        }
      ],
      "multi": true
    }
  ]
}

Results-before-update

After-update-existing-friend

After-update-not-existing-friend

There are alternative solutions,the simpler solution is to use $reduce,and dont do any checks,
it can be done with 1 array read,but the problem is that we use $concat which is slow if we
do too many of them like >500.(we dont have $push aggregator operator to add in arrays,we have it only as accumulator)

There is also another more complicated solution for big arrays,but here is not needed.
The only cost is that we read the array 2 times instead of 1.