MongoDB嵌套对象聚合求和排序

Question

我有高度嵌套的 mongodb 对象集，我想根据投票的总和结果对子文档进行排序，例如:

{
   "_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
              }
          ]
       }
    ]
}  

我想根据投票的总和结果对产品进行降序排序预期的输出是

{
   "_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
       }
    ]
}  

有什么想法吗？

Answer 1

下面的流水线

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" }
        }
    }
])  

会输出

{
    "_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
                }
            ]
        }
    ]
}

---

如果您希望将投票总和保持在预期输出中所示的产品级别，只需按如下方式修改 $project 阶段

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