Mongo查询可以拆分两个以上的数组，而不会产生重复的结果

Question

我正试图为下面提到的记录构建一个Mongo查询

[
{
"_id": "1",
"source": "Source1",
"customer": "customer1",
"models": [
{ "modelid": "modelid123" },
{ "modelid": "modelid124" }
],
"accounts": [
{
"account": "acc1",
"models": [
{ "modelid": "modelid123" },
{ "modelid": "modelid124" }
],
"packages": [
{
"package": "p1",
"models": [
{ "modelid": "modelid123" },
{ "modelid": "modelid125" }
]
}
]
},
{
"account": "acc2",
"models": [
{ "modelid": "modelid123" },
{ "modelid": "modelid126" }
],
"packages": [
{
"package": "p2",
"models": [
{ "modelid": "modelid123" }
]
}
]
}
]
}
]

我预期的o/p是

source    customer    account    package
Source1   customer1
Source1   customer1    acc1
Source1   customer1    acc1        p1
Source1   customer1    acc2
Source1   customer1    acc2        p2 

由于modelid123存在于上述所有级别。

我尝试使用$unvent聚合函数来实现上述功能，但我得到了重复的行。

db.collection.aggregate([
{ $unwind: { path: "$models", preserveNullAndEmptyArrays: true } },
{ $unwind: { path: "$accounts", preserveNullAndEmptyArrays: true } },
{ $unwind: { path: "$accounts.models", preserveNullAndEmptyArrays: true } },
{ $unwind: { path: "$accounts.packages.models", preserveNullAndEmptyArrays: true } },
{
$match: {
"$and": [ { "source": "Accurate" } ],
"$or": [
{ "models.modelId": { "$in": [ "model1234" ] } },
{ "accounts.models.modelId": { "$in": [ "model1234" ] } },
{ "accounts.packages.models.modelId": { "$in": [ "model1234" ] } }
]
}
}
])

`这里的问题是我无法重组数据。我可以使用Java在代码端实现这一点，但我也想实现分页。我正在使用Spring数据mongodb进行连接。

Answer 1

您可以使用$group来处理双工。我想你可以从我的答案中学习并改正你自己的答案。

db.collection.aggregate([
{ $unwind: { path: "$models", preserveNullAndEmptyArrays: true } },
{ $unwind: { path: "$accounts", preserveNullAndEmptyArrays: true } },
{ $unwind: { path: "$accounts.models", preserveNullAndEmptyArrays: true } },
{ $unwind: { path: "$accounts.packages", preserveNullAndEmptyArrays: true } },
{ $unwind: { path: "$accounts.packages.models", preserveNullAndEmptyArrays: true } },
{
$project: {
_id: 0,
source: "$source",
customer: "$customer",
account: {
$cond: {
if: { $eq: [ "$models.modelid", "modelid123" ] },
then: "$accounts.account",
else: null
}
},
package: {
$cond: {
if: {
$and: [
{ $eq: [ "$models.modelid", "modelid123" ] },
{ $eq: [ "$accounts.packages.models.modelid", "modelid123" ] }
]
},
then: "$accounts.packages.package",
else: null
}
}
}
},
{
$group: {
_id: "$$ROOT",
count: { $sum: 1 }
}
},
{
$replaceWith: "$_id"
}
])

https://mongoplayground.net/p/u-cMvnuao1t