MongoDB: How to upsert an object in a nested array?

user_10255450 0
Viewed 364

Consider the following document:

{
    "countries" : [ 
        {
            "country" : "France",
            "cities" : [ 
                {
                    "city" : "Paris",
                    "population" : 100
                }
            ]
        }, 
        {
            "country" : "England",
            "cities" : [ 
                {
                    "city" : "London",
                    "population" : 100
                }, 
                {
                    "city" : "Liverpool",
                    "population" : 100
                }
            ]
        }
    ]
}

I'd like to add or update cities (imagine we have the method upsert(country, city, population)

We have 3 cases:

  1. Country and city already exist - This is only an update (replacing the city object)
  2. Country exists but city doesn't - Need only to $push probably
  3. Country and city doesn't exist - Need to prepare and $push the country object with a cities array of size 1 that contains the new city.

Is it possible to combine all these three operations in one query?

By the way, I learned about $addToSet but for my understanding, it compares all the object's keys and it seems to me that it's not going to work since I also have cities which is an array.

Just updating is kinda easy with ArrayFilters:

q = {"_id":"1"}

update = {
    "$set": {
        "countries.$[i].cities.$[j].population": 200,

    }
} 

options = {
    "arrayFilters": [
        {
            "i.country": "France"
        },
        {
            "j.city": "Paris",
        },
    ],
}

db.collection.updateOne(q, update, options)
aggregation-framework mongodb-query mongodb
1 Answers

The regular update query will not upsert nested array, I would suggest you do it client-side, or if you really want to do in single query then you can try update with aggregation pipeline starting from MongoDB 4.2, it is a long process to do in an aggregation pipeline,

  • $map to iterate loop of array
  • $mergeObjects to merge current object with updated field
  • $cond to check if-else condition
  • $concatArrays to concat 2 arrays
var country = "France";
var city = {
  city: "Paris",
  population: 100
};

db.collection.update({},
 [{
    $set: {
      countries: {
        $cond: [
          { $in: [country, "$countries.country"] },
          {
            $map: {
              input: "$countries",
              in: {
                $cond: [
                  { $eq: [country, "$$this.country"] },
                  {
                    $mergeObjects: [
                      "$$this",
                      {
                        $cond: [
                          { $in: [city.city, "$$this.cities.city"] },
                          {
                            cities: {
                              $map: {
                                input: "$$this.cities",
                                in: {
                                  $cond: [
                                    { $eq: [city.city, "$$this.city"] },
                                    city,
                                    "$$this"
                                  ]
                                }
                              }
                            }
                          },
                          {
                            cities: {
                              $concatArrays: ["$$this.cities", [city]]
                            }
                          }
                        ]
                      }
                    ]
                  },
                  "$$this"
                ]
              }
            }
          },
          {
            $concatArrays: [
              "$countries",
              [
                {
                  country: country,
                  cities: [city]
                }
              ]
            ]
          }
        ]
      }
    }
  }]
)

Playground

user_89871280
Related