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?

Do you want to "$map" with something like this mongoplayground.net example? — rickhg12hs
– rickhg12hs, Commented Jul 6, 2024 at 15:30

ray · Accepted Answer · 2024-07-06 15:33:22Z

0

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

answered Jul 6, 2024 at 15:33

ray

15.6k12 gold badges96 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vgeorg Over a year ago

Perfect, thanks, much appreciated!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related