MongoDB多重/嵌套聚合

Question

我有这些集合:

users

{
_id: "userId1",
// ...
tracks: ["trackId1", "trackId2"],
};

tracks

{
_id: "trackId1",
// ...
creatorId: "userId1",
categoryId: "categoryId1"
}

categories

{
_id: "categoryId1",
// ...
tracks: ["trackId1", "trackId15", "trackId20"],
};

通过使用下面的代码，我能够通过它的ID获得一个轨道，并添加创建者

tracks.aggregate([
{
$match: { _id: ObjectId(trackId) },
},
{
$lookup: {
let: { userId: { $toObjectId: "$creatorId" } },
from: "users",
pipeline: [{ $match: { $expr: { $eq: ["$_id", "$$userId"] } } }],
as: "creator",
},
},
{ $limit: 1 },
])
.toArray();

反应:

"track": {
"_id": "trackId1",
// ...
"categoryId": "categoryId1",
"creatorId": "userId1",
"creator": {
"_id": "userId1",
// ...
"tracks": [
"trackId5",
"trackId10",
"trackId65"
]
}
}

但我正在努力的是，我希望creator.tracks聚合也通过它们的ID返回轨道(例如最多最后5个)，并且还从categoryId

获得最后5个轨道预期结果:

"track": {
"_id": "trackId1",
// ...
"categoryId": "categoryId1",
"creatorId": "userId1",
"creator": {
"_id": "userId1",
"tracks": [
{
"_id": "trackId5",
// the rest object without the creator
},
{
"_id": "trackId10",
// the rest object without the creator
},
{
"_id": "trackId65",
// the rest object without the creator
},
]
},
// without trackId1 which is the one that is being viewed
"relatedTracks": [
{
"_id": "trackId15",
// the rest object without the creator
},
{
"_id": "trackId20",
// the rest object without the creator
},
]
}

我将感谢任何解释/帮助我了解什么是最好的，这样做还能保持良好的性能

Answer 1

查询

• start from a track
• 与使用trackId的用户一起获得创建者的所有轨道(creator-tracks)
• 使用categoryId与类别连接以获得类别的所有曲目(相关曲目)
• 从相关音轨中删除创建者的音轨
• 使用$slice(creator-tracks和related-tracks)获取最后5个

*我增加了2个额外的查找，以获得所有信息的轨道，其空数组，因为我没有足够的数据(我只有trackId1)，所有的数据，它将工作

PlayMongo

db.tracks.aggregate([
{
"$match": {
"_id": "trackId1"
}
},
{
"$lookup": {
"from": "users",
"localField": "creatorId",
"foreignField": "_id",
"as": "creator-tracks"
}
},
{
"$set": {
"creator-tracks": {
"$arrayElemAt": [
"$creator-tracks.tracks",
0
]
}
}
},
{
"$lookup": {
"from": "categories",
"localField": "categoryId",
"foreignField": "_id",
"as": "related-tracks"
}
},
{
"$set": {
"related-tracks": {
"$arrayElemAt": [
"$related-tracks.tracks",
0
]
}
}
},
{
"$set": {
"related-tracks": {
"$filter": {
"input": "$related-tracks",
"cond": {
"$not": [
{
"$in": [
"$$this",
"$creator-tracks"
]
}
]
}
}
}
}
},
{
"$set": {
"creator-tracks": {
"$slice": [
{
"$filter": {
"input": "$creator-tracks",
"cond": {
"$ne": [
"$$this",
"$_id"
]
}
}
},
-5
]
}
}
},
{
"$set": {
"related-tracks": {
"$slice": [
"$related-tracks",
-5
]
}
}
},
{
"$lookup": {
"from": "tracks",
"localField": "creator-tracks",
"foreignField": "_id",
"as": "creator-tracks-all-info"
}
},
{
"$lookup": {
"from": "tracks",
"localField": "related-tracks",
"foreignField": "_id",
"as": "related-tracks-all-info"
}
}
])