CODEWITHSUNDEEP
mongodbaggregation-framework
colymore
colymore

Reputation: 12316

MongoDB aggregate $match and $group with $sum

i have a collection with documents like this:

{
    "Company" : "4433",
    "Descripcion" : "trabajo",
    "Referencia" : "11817",
    "HoraImportado" : "15:54",
    "ImportedOd" : "2014-05-20T13:54:28.493Z",
    "Items" : [],
    "Notes" : [ 
        {
            "_id" : ObjectId("537b5ea4c61b1d1743f43420"),
            "NoteDateTime" : "2014-05-20T13:54:44.418Z",
            "Description" : "nota",
            "IsForTechnician" : true,
            "Username" : "admin"
        }, 
        {
            "_id" : ObjectId("537c4a549e956f77ab8c7c38"),
            "NoteDateTime" : ISODate("2014-05-21T06:40:20.299Z"),
            "Description" : "ok",
            "IsForTechnician" : true,
            "Username" : "admin"
        }
    ],
    "OrderState" : "Review",
    "SiniestroDe" : "Emergencia",
    "Technicians" : [ 
        {
            "TechnicianId" : ObjectId("53465f9d519c94680327965d"),
            "Name" : "Administrator",
            "AssignedOn" : ISODate("2014-05-20T13:54:44.373Z"),
            "RemovedOn" : null
        }
    ],
    "TechniciansHistory" : [ 
        {
            "TechnicianId" : ObjectId("53465f9d519c94680327965d"),
            "Name" : "Administrator",
            "AssignedOn" : ISODate("2014-05-20T13:54:44.373Z"),
            "RemovedOn" : null
        }, 
        {
            "Name" : "Nuevo",
            "AssignedOn" : ISODate("2014-05-20T13:54:44.373Z"),
            "RemovedOn" : null,
            "TechnicianId" : ObjectId("5383577a994be8b9a9e3f01e")
        }
    ],
    "Telefonos" : "615554006",
    "_id" : ObjectId("537b5ea4c61b1d1743f4341f"),
    "works" : [ 
        {
            "code" : "A001",
            "name" : "Cambiar bombilla",
            "orderId" : "537b5ea4c61b1d1743f4341f",
            "price" : "11",
            "ID" : 33,
            "lazyLoaded" : true,
            "status" : 0,
            "Date" : ISODate("2014-05-21T06:40:20.299Z"),
            "TechnicianId" : "53465f9d519c94680327965d",
            "_id" : ObjectId("537c4a549e956f77ab8c7c39")
        }, 
        {
            "code" : "A001",
            "name" : "Cambiar bombilla",
            "orderId" : "537b5ea4c61b1d1743f4341f",
            "price" : "11",
            "ID" : 34,
            "lazyLoaded" : true,
            "status" : 0,
            "Date" : ISODate("2014-05-21T06:40:20.299Z"),
            "TechnicianId" : "53465f9d519c94680327965d",
            "_id" : ObjectId("537c4a549e956f77ab8c7c3a")
        }
    ]
}

Now i want to get the works for a selected TechnicianId array, group by TechnicianId and get the sum of the works.price for each technician.+

I try with this:

db.orders.aggregate([
                     { $match: { 'works.TechnicianId': {$in:['53465f9d519c94680327965d']}}},
                     { $group: { _id: "$works.TechnicianId",total:{$sum:'$works.price'}}},
                   ])

And this is the result:

{
    "result" : [ 
        {
            "_id" : [ 
                "53465f9d519c94680327965d", 
                "53465f9d519c94680327965d"
            ],
            "total" : 0
        }
    ],
    "ok" : 1
}

The total its the $sum but its 0 but should be 44.

Upvotes: 2

Views: 17762

Answers (3)

ftft
ftft

Reputation: 1

db.inventory.insert(
{
item: “ABC1”,
details: {
model: “14Q3”,
manufacturer: “XYZ Company”
},
stock: [ { size: “S”, qty: 25 }, { size: “M”, qty: 50 } ],
category: “clothing”
}
)

Upvotes: -1

RickN
RickN

Reputation: 13500

The price value is a string. $sum only operates on Numbers.

I've checked this by running the following:

db.foo.insert({"cost": "1"})
db.foo.insert({"cost": "2"})
db.foo.insert({"cost": "3"})
db.foo.insert({"cost": 4})
db.foo.insert({"cost": 5})
db.foo.aggregate([{$group: {_id: null, cost: {$sum: "$cost"}}}])
{ "result" : [ { "_id" : null, "cost" : 9 } ], "ok" : 1 }

According to this answer, you can't cast values in normal Mongo queries, so you can't change the string to a number inline.

You should either update all values to a Number datatype or use map-reduce. I'd go for the former.

If the value is a string to prevent floating point errors, consider multiplying by 100 to store the value in cents: "10.50" --> 1050

As Lalit Agarwal indicated, you'll also need to unwind the array of works. Example of what happens if you don't:

db.bar.insert({"works": [{price: 10}]})
db.bar.insert({"works": [{price: 20}, {price: 30}]})
db.bar.insert({"works": [{price: 40}, {price: 50}]})

db.bar.aggregate([ 
    {$group: {_id: null, total: {$sum: "$works.price"} }}
])
{ "result" : [ { "_id" : null, "total" : 0 } ], "ok" : 1 }

db.bar.aggregate([
    {$unwind: "$works"}, 
    {$group: {_id: null, total: {$sum: "$works.price"} }}
])
{ "result" : [ { "_id" : null, "total" : 150 } ], "ok" : 1 }

What $unwind does is make 5 documents out of the initial 3, all with a single value in the works field. It then groups and sums them.

Upvotes: 0

Lalit Agarwal
Lalit Agarwal

Reputation: 2354

Try adding unwind, 

db.orders.aggregate([
                 { $match: { 'works.TechnicianId': {$in:['53465f9d519c94680327965d']}}},
                 { $unwind: "$works" },
                 { $group: { _id: "$works.TechnicianId",total:{$sum:'$works.price'}}},
               ])

Look here for more info : http://docs.mongodb.org/manual/reference/operator/aggregation/unwind/

Upvotes: 2

Related Questions