结合$project、$unwind和$group的查询

Question

我有以下数据集：

{
  "_id" : ObjectId("57684f2b61f2af6d49fa6dbd"),
  "firstname" : "First1",
  "surname" : "Sur1",
  "email" : "[e-mail]",
  "goals" : [ 
    {
        "gId" : "base1",
        "recordDate" : ISODate("2016-06-21T20:05:48.972Z")
    }, 
    {
        "gId" : "base2",
        "recordDate" : ISODate("2016-06-21T20:05:48.972Z")
    }, 
    {
        "gId" : "base1",
        "recordDate" : ISODate("2016-06-21T20:05:48.972Z")
    }
 ]
}

我需要以下结果：

{
  "_id" : ObjectId("57684f2b61f2af6d49fa6dbd"),
  "firstname" : "First1",
  "surname" : "Sur1",
  "email" : "[e-mail]",
  "goals" : [
    {
       "gId" : "base1",
       "count" : 2
    },
    {
       "gId" : "base2",
       "count" : 1
    }
  ]
}

到目前为止，我玩了聚合查询，但我找不到我的问题的解决方案。我的查询看起来像那样，但它不起作用。第一点$project本身运行良好，$unwind和$group也是如此，但我不知道如何将它们组合在一起。

db.getCollection('users').aggregate(
{
  $project : {
    firstname: "$firstname",
    surname: "$surname",
    email: "$email",
    goals: "$goals"
  }
},
{ 
  $unwind: '$goals' 
},
{ 
  $group: {
    gid: '$goals.gId',
    count: {'$sum': 1}
  }
}
)

提前感谢，汤姆

Answer 1

使用以下管道尝试

db.getCollection('users').aggregate(
    { 
      $unwind: '$goals' 
    },
    { 
      $group: {
        _id: {
            firstname: "$firstname",
            surname: "$surname",
            email: "$email",
            gId: "$goals.gId"
        },
        count: {'$sum': 1}
      }
    },
    {
        $group: {
            _id: {
                firstname: "$_id.firstname",
                surname: "$_id.surname",
                email: "$_id.email"
            },
            goals: {
                $push: {
                    gId: "$_id.gId",
                    count: "$count"
                }
            }        
        }
    },
    {
        $project: {
            _id: 0,
            firstname: "$_id.firstname",
            surname: "$_id.surname",
            email: "$_id.email",
            goals: 1
        }
    }
)

结果如下所示

{
    "goals" : [ 
        {
            "gId" : "base2",
            "count" : 1.0
        }, 
        {
            "gId" : "base1",
            "count" : 2.0
        }
    ],
    "firstname" : "First1",
    "surname" : "Sur1",
    "email" : "[e-mail]"
}