Update every sub document: string manipulation

Hi everybody!

I have a very large mongodb database. In this database I have a datastructure similar to this (simplified):

{
    "_id": {"$oid": "626b9f8888daf0141b7069f0" },
    "tools": [
        {"name": "A", "output": "B"},
        {"name": "C", "output": "D"},
    ]
}

I am currently cleaning up this database. What I would like to achieve is to have a uniform lowercase representation of the field tools.name (which is not the case right now). I already tried different approaches, but none of them worked for me. My hope is to find anybody here who can help me out please :slight_smile:

Here are the approaches which I have tried so far:
Approach 1:

db.collection.updateMany({}, { $set: { "tools.$[].name": "X" } })

This performs an update operation on the correct fields, but I did not find a way to get the current value of the field, in order to perform a $toLower operation. I only achieved to set it to static values ("X").

Approach 2:
Use an aggregation pipeline to deconstruct the array, perform the $toLoweroperation:

    [
    {
      $unwind:
         /**
         * unwind tools, to get a document per element
         */
        {
            path: "$tools",
        },
    },
    {
    $set:
      /**
       * Perform the toLower operation
       */
      {
        "tools.name": {
          $toLower: "$tools.name",
        },
      },
    },
    {
    $group:
      /**
       * re create the array
       */
      {
        _id: "$_id",
        tools: {
          $push: "$tools",
        },
      },
    },
    ]

This almost does the job, at least it shows correct results in the compass pipeline editor. Anyway, the database does not accept the query when I use it in an updateMany statement like

db.collection.updateMany({}, [... pipeline from above ...])

as it reports the error $unwind is not allowed to be used within an update.

Can anybody help me with my operation?
Thanks a lot,
Philipp

You’re close! You need to use an aggregation pipeline that doesn’t do any $unwinding - only stages that do one-to-one document transformations are allowed in the update pipeline. All you need to do is $set (aka $addFields) of the tools field like this:

[ {$set:{ tools: {$map: {input: "$tools", in: { name: {$toLower: "$$this.name"}, output:"$$this.output"}}}}}]

This iterates over the tools array replacing each name with lower case version of the same name.

Asya

1 Like

Hi Asya!

Thank you so much. Your elegant solution works well! I did not come up with a solution using $map - which makes sense now.

Thanks,
Philipp

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.