How to use $set and dot notation to update embedded array elements using corresponding old element?

Question

I have following documents in a MongoDb:

from pymongo import MongoClient
client = MongoClient(host='my_host', port=27017)
database = client.forecast
collection = database.regions
collection.delete_many({})
regions = [
    {
        'id': 'DE',
        'sites': [
            {
                'name': 'paper_factory',
                'energy_consumption': 1000
            },
            {
                'name': 'chair_factory',
                'energy_consumption': 2000
            },
        ]
    },
    {
        'id': 'FR',
        'sites': [
            {
                'name': 'pizza_factory',
                'energy_consumption': 3000
            },
            {
                'name': 'foo_factory',
                'energy_consumption': 4000
            },
        ]
    }
]
collection.insert_many(regions)

Now I would like to copy the property sites.energy_consumption to a new field sites.new_field for each site:

set_stage = {
    "$set": {
        "sites.new_field": "$sites.energy_consumption"
    }
}

pipeline = [set_stage]
collection.aggregate(pipeline)

However, instead of copying the individual value per site, all site values are collected and added as an array. Intead of 'new_field': [1000, 2000] I would like to get 'new_field': 1000 for the first site:

{
    "_id": ObjectId("61600c11732a5d6b103ba6be"),
    "id": "DE",
    "sites": [
      {
        "name": "paper_factory",
        "energy_consumption": 1000,
        "new_field": [
          1000,
          2000
        ]
      },
      {
        "name": "chair_factory",
        "energy_consumption": 2000,
        "new_field": [
          1000,
          2000
        ]
      }
    ]
  },
  {
    "_id": ObjectId("61600c11732a5d6b103ba6bf"),
    "id": "FR",
    "sites": [
      {
        "name": "pizza_factory",
        "energy_consumption": 3000,
        "new_field": [
          3000,
          4000
        ]
      },
      {
        "name": "foo_factory",
        "energy_consumption": 4000,
        "new_field": [
          3000,
          4000
        ]
      }
    ]
  }

=> What expression can I use to only use the corresponding entry of the array?

Is there some sort of current-index operator:

$sites[<current_index>].energy_consumption

or an alternative dot operator (would remind me on difference between * multiplication and .* element wise matrix multiplication)?

$sites:energy_consumption

Or is this a bug?

Edit

I also tried to use the "$" positional operator, e.g. with

sites.$.new_field

or

$sites.$.energy_consumption

but then I get the error

FieldPath field names may not start with '$'

Related:

https://docs.mongodb.com/manual/reference/operator/aggregation/set/#std-label-set-add-field-to-embedded

In MongoDB how do you use $set to update a nested value/embedded document?

Answer 1

If the field is member of an array by selecting it you are selecting all of them.

{ar :[{"a" : 1}, {"a" : 2}]}

"$ar.a" = [1 ,2]

Also you cant mix update operators with aggregation, you cant use things like $sites.$.energy_consumption, if you are doing aggregation you have to use aggregate operators, with only exception the $match stage where you can use query operators.

Query

• alternative slightly different solution from yours using $setField
• i guess it will be faster, but probably little difference
• no need to use javascript it will be slower
• this is >= MongoDB 5 solution, $setField is new operator

Test code here

aggregate(
[{"$set":
  {"sites":
   {"$map":
    {"input":"$sites",
     "in":
     {"$setField":
      {"field":"new_field",
       "input":"$$this",
       "value":"$$this.energy_consumption"}}}}}}]
)

Answer 2

use $addFields

db.collection.update({},
[
  {
    "$addFields": {
      "sites": {
        $map: {
          input: "$sites",
          as: "s",
          in: {
            name: "$$s.name",
            energy_consumption: "$$s.energy_consumption",
            new_field: {
              $map: {
                input: "$sites",
                as: "value",
                in: "$$value.energy_consumption"
              }
            }
          }
        }
      }
    }
  }
])

mongoplayground

Answer 3

I found following ugly workarounds that set the complete sites instead of only specifying a new field with dot notation:

a) based on javascript function

set_stage = {
    "$set": {
        "sites": {
            "$function": {
                "body": "function(sites) {return sites.map(site => {site.new_field = site.energy_consumption_in_mwh; return site})}",
                "args": ["$sites"],
                "lang": "js"
            }
        }
    }
}

b) based on map and mergeObjects

set_stage = {
    "$set": {
        "sites": {
            "$map": {
                "input": "$sites",
                "in": {
                    "$mergeObjects": ["$$this", {
                        "new_field": "$$this.energy_consumption_in_mwh"
                    }]
                }
            }
        }
    }
}

If there is some kind of $$this context for the dot operator expression, allowing a more elegant solution, please let me know.