Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Query that combines $project, $unwind & $group

I have the following data set:

{
  "_id" : ObjectId("57684f2b61f2af6d49fa6dbd"),
  "firstname" : "First1",
  "surname" : "Sur1",
  "email" : "[email protected]",
  "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")

    }
 ]
}

I need the following result:

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

So far I played around with the aggregate query but I couldn't find the solution to my problem. My query looks like that but it doesn't work. The first bit $project runs fine on its own and so does $unwind and $group but I don't know how I can combine it together.

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

Thanks in advance, Tom

like image 797
Thomas Dittmar Avatar asked Jul 11 '16 04:07

Thomas Dittmar


1 Answers

Try it with the following pipeline

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

Result looks like this

{
    "goals" : [ 
        {
            "gId" : "base2",
            "count" : 1.0
        }, 
        {
            "gId" : "base1",
            "count" : 2.0
        }
    ],
    "firstname" : "First1",
    "surname" : "Sur1",
    "email" : "[email protected]"
}
like image 64
DAXaholic Avatar answered Oct 14 '22 13:10

DAXaholic