Mongoose/MongoDB: Add Three Fields Together in Child Array-CodePudding

I am currently trying to create a temporary field that adds three fields together and then query based on a value that a user inputs (they only see the calculated and need it to match)

My biggest problem has been getting the projection to work as I need it. My field layout looks like this:

{
  _id: ObjectId(),
  userId: "",
  orderNumber: "",
  items: [
           {
             _id: ObjectId(),
             rateInfo: {
                         baseRate: 100,
                         tolls: 50,
                         fees: 25,
                       },
           },
           {
             _id: ObjectId(),
             rateInfo: {
                         baseRate: 200,
                         tolls: 25,
                         fees: 10,
                       },
           },
           {
             _id: ObjectId(),
             rateInfo: {
                         baseRate: 25,
                         tolls: 5,
                         fees: 0,
                       },
           },
         ],
}

I need to be able to add all three fields inside rateInfo and display them in each item array to look like so:

{
  _id: ObjectId(),
  userId: "",
  orderNumber: "",
  items: [
           {
             _id: ObjectId(),
             rateInfo: {
                         baseRate: 100,
                         tolls: 50,
                         fees: 25,
                       },
             rateTotal: 175,
           },
           {
             _id: ObjectId(),
             rateInfo: {
                         baseRate: 200,
                         tolls: 25,
                         fees: 10,
                       },
             rateTotal: 235,
           },
           {
             _id: ObjectId(),
             rateInfo: {
                         baseRate: 25,
                         tolls: 5,
                         fees: 0,
                       },
             rateTotal: 30,
           },
         ],
}

I have tried the follow snippets but neither seem to work for me:

The $reduce method sums all from the items array and then put the total at the order level like so:

{
  rateTotal: 440
  document: {
    _id: ObjectId(),
    userId: "",
    orderNumber: "",
    items: [
             {
               _id: ObjectId(),
               rateInfo: {
                           baseRate: 100,
                           tolls: 50,
                           fees: 25,
                         },
             },
             {
               _id: ObjectId(),
               rateInfo: {
                           baseRate: 200,
                           tolls: 25,
                           fees: 10,
                         },
             },
             {
               _id: ObjectId(),
               rateInfo: {
                           baseRate: 25,
                           tolls: 5,
                           fees: 0,
                         },
             },
           ],
  }
}

The $unwind method only adds up the first records like so:

{
  rateTotal: 175
  document: {
    _id: ObjectId(),
    userId: "",
    orderNumber: "",
    items: [
             {
               _id: ObjectId(),
               rateInfo: {
                           baseRate: 100,
                           tolls: 50,
                           fees: 25,
                         },
             },
             {
               _id: ObjectId(),
               rateInfo: {
                           baseRate: 200,
                           tolls: 25,
                           fees: 10,
                         },
             },
             {
               _id: ObjectId(),
               rateInfo: {
                           baseRate: 25,
                           tolls: 5,
                           fees: 0,
                         },
             },
           ],
  }
}

If anyone can provide some clarity on what I'm doing wrong and how to correct, it would be greatly appreciated.

NOTE: mongoose virtuals are not possible since I need to query it. I cannot backfill a calculated field inside the mongoose schema (using a custom setter or pre save).

CodePudding user response：

You can use $map to map to your desired structure.

db.collection.aggregate([
  {
    "$addFields": {
      "items": {
        "$map": {
          "input": "$items",
          "as": "i",
          "in": {
            _id: "$$i._id",
            rateInfo: {
              baseRate: "$$i.rateInfo.baseRate",
              tolls: "$$i.rateInfo.tolls",
              fees: "$$i.rateInfo.fees"
            },
            rateTotal: {
              "$add": [
                "$$i.rateInfo.baseRate",
                "$$i.rateInfo.tolls",
                "$$i.rateInfo.fees"
              ]
            }
          }
        }
      }
    }
  }
])

Here is the Mongo playground for your reference.

For your trials, you have figured out why for your $reduce trial. For your $unwind trial, the result is actually correct. Just the array is "flatten" by the array. You should actually be able to see multiple documents, as they are corresponding to each of the array entries. As you are just viewing the first document, so you are viewing the result of the first array element.