Add a field inside an array within a MongoDB object, with values computed dynamically based on other values inside the same nested array

Question

I have a simple MongoDB array of objects with this structure:

[
  {
    "_id": {
      "$oid": "6688c2f6b79f2bfefb751d5f"
    },
    "date": "06/07/2024",
    "serviceStatus": [
      {
        "home": "http://www.bbc.co.uk/ontologies/passport/home/Marathi",
        "count": 8
      },
      {
        "home": "http://www.bbc.co.uk/ontologies/passport/home/Indonesia",
        "count": 4
      },
   ]
  },
  {
    "_id": {
      "$oid": "66860a80b79f2bfefb7513cc"
    },
    "date": "04/07/2024",
    "serviceStatus": [
      {
        "home": "http://www.bbc.co.uk/ontologies/passport/home/Pashto",
        "count": 10
      },
      {
        "home": "http://www.bbc.co.uk/ontologies/passport/home/Zhongwen",
        "count": 4
      }
    ]
  }
]

I've tried adding a new field in the serviceStatus arrays, using this aggregation pipeline command, to get an abbreviated version of the serviceStatus.home value in each object:

{
    $addFields: {
      "serviceStatus.homeShort": { $substrCP: [ "$serviceStatus.home", 0, 2 ] },
    }

However this returns an error:

PlanExecutor error during aggregation :: caused by :: can't convert from BSON type array to String The command works fine if it is used to assign a static value, but does not work dinamically.

Is there anyway to add a field in a nested array in a MongoDB collection, using the aggregation pipeline, and populate it based on values already existing within the same array? Or does this require unwinding the arrays in separate objects, adding the field to each one, and then re-grouping them?

Answer 1

Since you are working on an array field, you will need to use $map to iterate through the entries and use $mergeObjects to append the new field to the objects. The $merge is for updating back into the collection and you can skip that if you do not need it.

db.collection.aggregate([
  {
    "$set": {
      "serviceStatus": {
        "$map": {
          "input": "$serviceStatus",
          "as": "ss",
          "in": {
            "$mergeObjects": [
              "$$ss",
              {
                "homeShort": {
                  "$substrCP": [
                    "$$ss.home",
                    0,
                    2
                  ]
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    "$merge": {
      "into": "collection"
    }
  }
])

Mongo Playground