MongoDB nested object aggregation sum and sort

Question

I have highly nested mongodb set of objects and i want to sort subofdocuments according to the result of sum their votes for example :

{
   "_id":17846384es,
   "company_name":"company1",
   "products":[
       {
         "product_id":"123785",
         "product_name":"product1",
         "user_votes":[
              {
                 "user_id":1,
                  "vote":1
              },
              {
                 "user_id":2,
                  "vote":2
              }
          ]
       },
       {
         "product_id":"98765",
         "product_name":"product2",
         "user_votes":[
              {
                 "user_id":5,
                  "vote":3
              },
              {
                 "user_id":3,
                  "vote":3
              }
          ]
       }
    ]
}

i want to sort as descending products according to the result of sum their votes the expected output is

{
   "_id":17846384es,
   "company_name":"company1",
   "products":[
       {
         "product_id":"98765",
         "product_name":"product2",
         "user_votes":[
              {
                 "user_id":5,
                  "vote":3
              },
              {
                 "user_id":3,
                  "vote":3
              }
          ]
        "votes":8
       },
       {
         "product_id":"123785",
         "product_name":"product1",
         "user_votes":[
              {
                 "user_id":1,
                  "vote":1
              },
              {
                 "user_id":2,
                  "vote":2
              }
          ],
          "votes":3
       }
    ]
}

Any Idea ?

DAXaholic · Accepted Answer · 2016-08-21 07:48:28Z

2

The following pipeline

db.products.aggregate([
    { $unwind: "$products" },
    {
        $project: {
            company_name: 1,
            products: 1,
            totalVotes: {
                $sum: "$products.user_votes.vote"
            }
        }
    },
    { $sort: { totalVotes: -1 } },
    {
        $group: {
            _id: "$_id",
            company_name: { $first: "$company_name" },
            products: { $push: "$products" }
        }
    }
])

would output

{
    "_id" : "17846384es",
    "company_name" : "company1",
    "products" : [ 
        {
            "product_id" : "98765",
            "product_name" : "product2",
            "user_votes" : [ 
                {
                    "user_id" : 5,
                    "vote" : 3
                }, 
                {
                    "user_id" : 3,
                    "vote" : 3
                }
            ]
        }, 
        {
            "product_id" : "123785",
            "product_name" : "product1",
            "user_votes" : [ 
                {
                    "user_id" : 1,
                    "vote" : 1
                }, 
                {
                    "user_id" : 2,
                    "vote" : 2
                }
            ]
        }
    ]
}

In case you want to keep the sum of the votes at the product level as shown in your expected output simply modify the $project stage as follows

...
{
    $project: {
        company_name: 1,
        products: {
            product_id: 1,
            product_name: 1,
            user_votes: 1,
            votes: { $sum: "$products.user_votes.vote" }
        }
    }
}
...

edited Aug 21, 2016 at 7:48

answered Aug 21, 2016 at 5:39

DAXaholic

36k6 gold badges84 silver badges77 bronze badges

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

3 Comments

amer alhosary Over a year ago

can i get this result with company information in one result??

DAXaholic Over a year ago

Please show your expected output to avoid any misunderstandings

Wake Over a year ago

Just a note that $sum within the $project stage is only available in MongoDB 3.2 and up.

Collectives™ on Stack Overflow

MongoDB nested object aggregation sum and sort

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related