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 latest object of Total Sales Array.

I have tried as follow

db.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

I need to fix it only to the current path of that Products.totalSales will be; now it is giving from every products,

The code I have tried till now is as follows

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

I need to set the 0 to the index of the Product currently in. please let me know if there is other workaround

The expected output is following like this and I want to add the new fields 'totalSales' like the comments

{
  "Company": "Alpha ",
  "Products": [
    {
      "ProductName": "Bike",
      "Release_Date": "",
      "Sales": {
        "Today_Sales": [
          {
            "Date": "220821",
            "Sales": 12
          },
          {
            "Date": "220822",
            "Sales": 34
          }
        ],
        "Total_Sales": [
          {
            "Date": "220821",
            "Sales": 12
          },
          {
            "Date": "220822",
            "Sales": 34
          }
        ]
      },
      "totalSales": 34 //From the Sales of its last Sales Objects from its Total_Sales Array
    },
    {
      "ProductName": "Wheel",
      "Release_Date": "",
      "Sales": {
        "Today_Sales": [
          {
            "Date": "220821",
            "Sales": 11
          },
          {
            "Date": "220822",
            "Sales": 21
          }
        ],
        "Total_Sales": [
          {
            "Date": "220821",
            "Sales": 3
          },
          {
            "Date": "220822",
            "Sales": 128
          }
        ]
      },
      "totalSales": 128 //From the Sales of its last Sales Objects from its Total_Sales Array
    },
    {
      "ProductName": "Handle",
      "Release_Date": "",
      "Sales": {
        "Today_Sales": [
          {
            "Date": "220821",
            "Sales": 9
          },
          {
            "Date": "220822",
            "Sales": 28
          }
        ],
        "Total_Sales": [
          {
            "Date": "220821",
            "Sales": 5
          },
          {
            "Date": "220822",
            "Sales": 11
          }
        ]
      },
      "totalSales": 11 //From the Sales of its last Sales Objects from its Total_Sales Array
    }
  ]
}

CodePudding user response：

You were very close, the approach you took using pipelined updates is the correct one. You just need to clean up the syntax a little, like so:

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

Mongo Playground