Fixing the array index of two nested arrays

Hello Mongo Community,
I have the following document structure;

{
      Company: 'Alpha ',
      Products: [
            {
                  ProductName: ' Bike',
                  Release_Date: '',
                  Sales :
                  {
                  Today_Sales: [{Date: '...', Sales : 12},{Date: '...', Sales: 34}]
                  Total_Sales:[{Date: '...', Sales : 12},{Date: '...', Sales: 34}]
                  },
               {
             ...
           }
      ]
}

what I want to do is setting a new field at each product with using the values of Sales from Total Sales Array.

I have tried as follow

db.collection('collection').updateMany({},[
{ 
$set: { "Products.totalSales" : "$Products.Sales.Total_Sales.Sales"
}}
]

What I need help is I don’t know how to fix the array indexs in the above synatx

Products.Sales.Total_Sales.Sales

For Products : I need to fix it only to the current path of that Products.totalSales will be; now is giving from every products,
For Total_Sales : I want the last objects from its array,

I have tried the

$last: {Products.Sales.Total_Sales.Sales}

which is giving me the last product
&

Products.Sales.Total_Sales.0.Sales

and giving me nothing as it is searching 0 key at the Sales I think.

I have managed to set the last element of Total_Sales by this

db.collection('').updateMany({},
 [ {
      $set: {
          'Albums.totalSales': {
            $arrayElemAt:[{
              $map: {
                  input:'$Albums',
                  in: { $arrayElemAt: [ '$$this.Sales.Total_Sales.Sales',  -1 ] } 
                      
              }
          },0]}
       }
    }]
  )```
No idea how to set the Products only to the current path of **totalSales** will be placed.

I am not sure of what you want to achieve.

Sometimes you refer to Products.totalSales and at other times you write about Albums.totalSales. It is 2 different fields?

The object

is not valid. You cannot have 2 fields within the same object that have the same key. Well, actually you can, but only the last occurrence is kept my most JSON implementation. It looks like you have introduced errors while redacting the documents you published.

Please include real sample non-redacted documents and the exact result that you want.

Sorry my bad Albums is an actually a typo, I meant Products. I copied some codes from forums and tested so there was a messup.
What I want to achieve is I want to add a new Field named totalSales in every element inside Products Array and its value will be last Sales Object of that product’s Total_Sales.
The problem I am facing is I am getting the array of every products’ last sales object in each products.

As already mentioned, please

Sounds like you want to transform the Products array by doing something like this:

db.collection('collection').updateMany({},[{
    $set: { "Products" : {$map:{
          "input": "$Products",
          "in": {$mergeObjects:[ 
              "$$this",
              { "totalSales": "$$this.Sales.Total_Sales.Sales"}
          ]}
    }}}
])

Now, this will set in each Products subdocument a new field called totalSales which will have an array of Sales values from Total_Sales array. If you actually wanted a sum of these values, just add $sum operator like this: { "totalSales": {$sum:"$$this.Sales.Total_Sales.Sales"}}.

Asya

1 Like

Thanks , this solved the problem by a little modification.

db.collection.updateMany({},
[
  {
    $set: {
      Products: {
        $map: {
          input: "$Products",
          in: {
            $mergeObjects: [
              "$$this",
              {
                totalSales: {
                  $arrayElemAt: [
                    "$$this.Sales.Total_Sales.Sales",
                    -1
                  ]
                },
                
              }
            ]
          }
        }
      }
    }
  }
])

It’s always “$$this” - it’s a builtin variable which points to each element of array you iterate over with “$map”.

1 Like

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