Aggregate inside document in mongodb

Question

I have document sales

db.sale.findOne({_id : ObjectId("52ea4dd29dbc7923107ddb97")})

{
    "_id" : ObjectId("52ea4dd29dbc7923107ddb97"),
    "firm" : ObjectId("52e56c009dbc794999ea5c3d"),
    "patient" : {
        "last" : "",
        "first" : ""
    },
    "doc" : "",
    "hospital" : "",
    "discount" : 0,
    "dd" : "",
    "mode" : "cash",
    "invoice" : "300114-undefined-1",
    "items" : [
        {
            "bat" : "BJFE",
            "narco" : 0,
            "name" : "GDRNCCD",
            "mrp" : 1,
            "free" : 0,
            "qty" : 1,
            "item_discount" : 0,
            "wpr" : 1,
            "exp" : "1425168000000"
        },
        {
            "bat" : "",
            "narco" : 0,
            "name" : "GDRN vbhjdsfb",
            "mrp" : 1,
            "free" : 0,
            "qty" : 1,
            "item_discount" : 0,
            "wpr" : 0,
            "exp" : "[object Object]"
        },
        {
            "bat" : "",
            "narco" : 0,
            "name" : "GDRN vbhjdsfb",
            "mrp" : 1,
            "free" : 0,
            "qty" : 1,
            "item_discount" : 0,
            "wpr" : 0,
            "exp" : "[object Object]"
        }
    ],
    "date" : ISODate("2014-01-30T00:00:00Z"),
    "mob" : "",
    "email" : ""
}

How can I Aggregate total numbers if items in one field and sum up mrp *qty of all the items into one field. I have read the mognodb aggregation but it only aggregates among group of matched documents not inside a single document. Is it possile?

{
    "_id" : ObjectId("52ea4dd29dbc7923107ddb97"),
    "firm" : ObjectId("52e56c009dbc794999ea5c3d"),
     'total_items' : items.length,
      "total" :     mrp*qty of all items,
}

Ori Dar · Accepted Answer · 2014-01-30 13:53:10Z

1

db.sales.aggregate( 
    {$unwind: "$items"},
    {$project: {_id: 1,firm:1, total: {$multiply: ["$items.mrp", "$items.qty"]}}},
    {$group: {_id: {"id":"$_id", firm:"$firm"}, count: {$sum:1} , total : {$sum:"$total"}}}
)

With a slight change : _id contains id and firm, you will need an extra projection to match your desired document, but I don't think it's important.

Plus, you can easily change to group by farm only

edited Jan 30, 2014 at 13:53

answered Jan 30, 2014 at 13:47

Ori Dar

19.1k5 gold badges61 silver badges76 bronze badges

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

Comments

Praveen Singh Yadav · Accepted Answer · 2014-01-30 21:28:57Z

0

Thanks to Orid,I tried this,

db.sale.aggregate(

{ $match :{ firm :ObjectId("52e56c009dbc794999ea5c3d") }  }, 

{$project : {day : {$dayOfMonth : '$date'},items :1,patient :1,invoice :1}}, 

{$match : {day: {$gte : new Date().getDate()}}},

 {$unwind : "$items"}, 

{$project: {_id: 1,patient:1,invoice :1, total: {$multiply: ["$items.mrp", "$items.qty"]}}},  

{$group: {_id: {"id":"$_id", invoice:"$invoice",patient :"$patient"}, count: {$sum:1} , total : {$sum:"$total"}}})

Result

{
    "result" : [
        {
            "_id" : {
                "id" : ObjectId("52eab6129dbc7923107ddbaf"),
                "invoice" : "310114-undefined-1",
                "patient" : {
                    "last" : "",
                    "first" : ""
                }
            },
            "count" : 2,
            "total" : 25
        },
        {
            "_id" : {
                "id" : ObjectId("52eab6129dbc7923107ddbb0"),
                "invoice" : "310114-undefined-1",
                "patient" : {
                    "last" : "",
                    "first" : ""
                }
            },
            "count" : 1,
            "total" : 1
        },
        {
            "_id" : {
                "id" : ObjectId("52eab6129dbc7923107ddbae"),
                "invoice" : "310114-undefined-1",
                "patient" : {
                    "last" : "",
                    "first" : ""
                }
            },
            "count" : 2,
            "total" : 5
        }
    ],
    "ok" : 1
}

answered Jan 30, 2014 at 21:28

Praveen Singh Yadav

1,8614 gold badges20 silver badges32 bronze badges

1 Comment

Praveen Singh Yadav Over a year ago

this in python works:>>> db.sale.aggregate( ... [{ '$match' :{ 'firm' :ObjectId("52e56c009dbc794999ea5c3d") } }, {'$project' : {'day' : {'$dayOfMonth' : '$date'},'items' :1,'patient' :1,'invoice' :1}}, ... {'$match' : {'day': {'$gte' : date.today().day}}}, ... {'$unwind' : '$items'}, ... {'$project': {'_id': 1,'patient':1,'invoice' :1, 'total': {'$multiply': ['$items.mrp', '$items.qty']}}}, ... {'$group': {'_id': {'id':'$_id', 'invoice':'$invoice','patient' :'$patient'}, 'count': {'$sum':1} , 'total' : {'$sum':'$total'}}} ... ])

Collectives™ on Stack Overflow

Aggregate inside document in mongodb

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related