How to use aggregation pipeline for updating multiple documents and insert items in an array if not found by object property value

I am very new to MongoDB and I need to do a somewhat complex Update operation on my collection.

I have this kind of collection:

[
  {
    "Id": 1,
    "extension": [
      {
        "key": "Name",
        "value": "Bob"
      },
      {
        "key": "Age",
        "value": 20
      }
    ]
  },
  {
    "Id": 2,
    "extension": [
      {
        "key": "Name",
        "value": "Sam"
      }
    ]
  },
  {
    "Id": 3,
    "extension": [
      {
        "key": "Age",
        "value": 25
      }
    ]
  },
  {
    "Id": 4
  }
]

I want to update my collection so that every document ends with an extension array that contains an item with Key Name and also an item with Key Age.

There are 4 types of existing documents in my collection:

  • A document missing the extension property altogether. For these, I want to create the extension array and insert 2 items. One with key Name and value “Default” and another item with key Age and value 99.
  • A document missing an item with key Name in its extension array. I want to insert an item with key Name and value “Default”.
  • A document missing an item with key Age in its extension array. I want to insert an item with key Age and value 99.
  • A document that already has an item with a key Name and an item with a key Age in its extension array. I don’t want to update these.

I want to use aggregation pipeline to update my collection, but I am not sure what to use as the query and as the update commands to achieve that.

I think I need something like that:

{
    update: "MyCollection",
    updates: [
        {
            q: { xxxxxxxxxx },
            u: [ 
                    extensions: {
                        yyyyyyyyyyyyyy
                    }
                ],
            multi: true
        }
    ]
}

In the end, my collection above would look like that after the update:

[
  {
    "Id": 1,
    "extension": [
      {
        "key": "Name",
        "value": "Bob"
      },
      {
        "key": "Age",
        "value": 20
      }
    ]
  },
  {
    "Id": 2,
    "extension": [
      {
        "key": "Name",
        "value": "Sam"
      },
      {
        "key": "Age",
        "value": 99
      }
    ]
  },
  {
    "Id": 3,
    "extension": [
      {
        "key": "Name",
        "value": "Default"
      },
      {
        "key": "Age",
        "value": 25
      }
    ]
  },
  {
    "Id": 4,
    "extension": [
      {
        "key": "Name",
        "value": "Default"
      },
      {
        "key": "Age",
        "value": 99
      }
    ]
  }
]

Answer

Option -1

You can do it by running two update queries

1) Add Age

Demo – https://mongoplayground.net/p/S12_Efvwa7d

db.collection.update(
    { "extension.key": { $ne: "Age" } },
    { $push: { "extension": { "key": "Age", "value": 99 } } },
    { multi: true }
)

2) Add Name

Demo – https://mongoplayground.net/p/Nkh4WS30tvC

db.collection.update(
    { "extension.key": { $ne: "Name" } },
    { $push: { "extension": { "key": "Name", "value": "Default" } } },
    { multi: true }
)

$push

db.collection.update


Option -2

You can combine them and make a single query using db.collection.bulkWrite

db.collection.bulkWrite( [
    { 
        updateMany :
        {
         "filter": { "extension.key": { $ne: "Name" } },
         "update": { $push: { "extension": { "key": "Name", "value": "Default" } } }
        }
   },
   { 
        updateMany :
        {
         "filter": { "extension.key": { $ne: "Age" } },
         "update": { $push: { "extension": { "key": "Age", "value": 99 } } }
        }
   }
] )