mongodb - adding the value in a field to the value in an embedded array

Question

I have a document in MongoDB as below.

{                                                              
  "CorePrice" : 1,
  "_id" : 166,
  "partno" : 76,
  "parttype" : "qpnm",
  "shipping" :
    [
      {
        "shippingMethod1" : "ground",
        "cost1" : "10"
      },
      {
        "shippingMethod2" : "air",
        "cost2" : "11"
      },
      {                                                              
        "shippingMethod3" : "USPS",
        "cost3" : "3"
      },
      {
        "shippingMethod4" : "USPS",
        "cost4" : 45
      }
    ]
}

My goal is to add CorePrice (1) to cost4 (45) and retrieve the computed value as a new column "dpv". I tried using the below query. However I receive an error exception: $add only supports numeric or date types, not Array. I'm not sure why. Any kind of help will be greatly appreciated.

db.Parts.aggregate([
  {
    $project: {
      partno: 1,
      parttype: 1,
      dpv: {$add: ["$CorePrice","$shipping.cost1"]}
    }
  },
  {
    $match: {"_id":{$lt:5}}
  }
]);

Philipp · Accepted Answer · 2013-09-30 10:59:31Z

1

When you refer to the field shipping.cost1 and shipping is an array, MongoDB does not know which entry of the shipping-array you are referring to. In your case there is only one entry in the array with a field cost1, but this can't be guaranteed. That's why you get an error.

When you are able to change your database schema, I would recommend you to turn shipping into an object with a field for each shipping-type. This would allow you to address these better. When this is impossible or would break some other use-case, you could try to access the array entry by numeric index (shipping.0.cost1).

Another thing you could try is to use the $sum-operator to create the sum of all shipping.cost1 fields. When there is only one element in the array with a field cost1, the result will be its value.

answered Sep 30, 2013 at 10:59

Philipp

70.1k10 gold badges121 silver badges159 bronze badges

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

1 Comment

user2829596 Over a year ago

"Another thing you could try is to use the $sum-operator to create the sum of all shipping.cost1 fields. When there is only one element in the array with a field cost1, the result will be its value." This sounds like an idea. I apologize but I am really a newbie to MongoDB. Do you know if I can accomplish this using mongo shell or should I write a Java program?

Community · Accepted Answer · 2016-09-22 16:59:44Z

0

I am able to achieve this by divorcing the query into two as below.

var pipeline1 = [
    {
        "$unwind": "$shipping"
    }, 
    {
        $project:{
            partno:1, 
            parttype:1,
            dpv:{
                $add:["$CorePrice","$shipping.cost4"]
            }
        }
    }, 
    {
        $match:{"_id":5}
    }
];               

R = db.tb.aggregate( pipeline );

edited Sep 22, 2016 at 16:59

CommunityBot

11 silver badge

answered Oct 6, 2013 at 5:29

user2829596

114 bronze badges

Collectives™ on Stack Overflow

mongodb - adding the value in a field to the value in an embedded array

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related