MongoDB聚合以获取count和Y样本条目

Question

MongoDB版本：4.2.17。

正在尝试对集合中的数据进行聚合。

示例数据：

{
"_id" : "244",
"pubName" : "p1",
"serviceIdRef" : "36e9c779-7865-4b74-a30b-e4d6a0cc5295",
"serviceName" : "my-service",
"subName" : "c1",
"pubState" : "INVITED"
}

我想：

通过某些东西(比如subName(和group by、serviceIdRef和limit进行匹配以返回X个条目此外，对于serviceIdRefs中的每一个，返回ACTIVE或INVITED状态中的文档的count。并且Y(对于这个例子，假设Y=3(处于这种状态的文档。例如，输出将显示为(简而言之(：

[
{
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", // For this state, repeat as we did for "INVITED" state above.
......
}
]
}
{
repeat for another service
}
]

到目前为止，我已经写了这篇文章，但无法获得那些Y条目。有(更好的(方法吗？

这就是我到目前为止所拥有的(不完整，也不完全以上面的格式输出(：

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
}

}
}
]
}
}

])

Answer 1

您必须执行第二个$group阶段来管理嵌套结构

• $match您的条件
• $sort按_id降序排列
• $group通过serviceIdRef和pubState，获取第一个必需字段并为sample准备数组，并获取文档数
• 仅用serviceIdRef代替$group，构建state阵列
• $slice用于限制sample中的文档

db.collection.aggregate([
{
$match: {
subName: { $in: ["c1", "c2"] },
pubState: { $in: ["INVITED", "ACTIVE"] }
}
},
{ $sort: { _id: -1 } },
{
$group: {
_id: {
serviceIdRef: "$serviceIdRef",
pubState: "$pubState"
},
serviceName: { $first: "$serviceName" },
sample: { $push: "$$ROOT" },
count: { $sum: 1 }
}
},
{
$group: {
_id: "$_id.serviceIdRef",
serviceName: { $first: "$serviceName" },
state: {
$push: {
pubState: "$_id.pubState",
count: "$count",
sample: { $slice: ["$sample", 22] }
}
}
}
}
])

游乐场