Mongoose/MongoDB: Add Three Fields Together in Child Array

Question

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

ray · Accepted Answer · 2022-04-19 21:09:55Z

1

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.

answered Apr 19, 2022 at 21:09

ray

15.6k12 gold badges100 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gvanriper Over a year ago

Confirmed this worked as I needed. The only other thing I did was project the root with all items and then reassigned back to items: document: "$$ROOT.items". I had other fields I needed to copy. Just putting here so everyone else that looks at this may need it.

Collectives™ on Stack Overflow

Mongoose/MongoDB: Add Three Fields Together in Child Array

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related