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
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]"
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With