Trying out aggregation on data in a collection. Example data:
{
"_id" : "244",
"pubName" : "p1",
"serviceIdRef" : "36e9c779-7865-4b74-a30b-e4d6a0cc5295",
"serviceName" : "my-service",
"subName" : "c1",
"pubState" : "INVITED"
}
I would like to:
- Do a match by something (let’s say subName) and and group by
serviceIdRef
and then limit to return X entries - Also return for each of the
serviceIdRefs
, the count of the documents in each ofACTIVE
orINVITED
states. And Y (for this example, say Y=3) documents that are in this state.
For example, the output would appear as (in brief):
[
{
serviceIdRef: "36e9c779-7865-4b74-a30b-e4d6a0cc5295",
serviceName:
state:[
{
pubState: "INVITED"
count: 200
sample: [ // Get those Y entries (here Y=3)
{
// sample1 like:
"_id" : "244",
"pubName" : "p1",
"serviceIdRef" : "36e9c779-7865-4b74-a30b-e4d6a0cc5295",
"serviceName" : "my-service",
"subName" : "c1",
"pubState" : "INVITED"
},
{
sample2
},
{
sample3
}
]
},
{
pubState: "ACTIVE",
......
}
]
}
{
repeat for another service
}
]
So far I have written this but am not able to get those Y entries. Is there another (better) way?
This is what I have so far:
db.sub.aggregate(
[{
$match:
{
"subName": {
$in: ["c1", "c2"]
},
"$or": [
{
"pubState": "INVITED",
},
{
"pubState": "ACTIVE",
}
]
}
},
{
$group: {
_id: "$serviceIdRef",
subs: {
$push: "$$ROOT",
}
}
},
{
$sort: {
_id: -1,
}
},
{
$limit: 22
},
{
$facet:
{
facet1: [
{
$unwind: "$subs",
},
{
$group:
{
_id: {
"serviceName" : "$_id",
"pubState": "$subs.pubState",
"subState": "$subs.subsState"
},
count: {
$sum: 1
}
}
}
]
}
}
])