I'm struggling to put a query together that aggregates the data exactly as I want. I would be so grateful if anyone can help with this!
The collections are:
Collection 1
[
{
"_id": 1,
"name": "Collection 1:1",
"collection_2_ids": [5, 6]
},
{
"_id": 2,
"name": "Collection 1:2",
"collection_2_ids": [8, 9]
}
]
Collection 2
[
{
"_id": 5,
"name": "collection 2:5",
"collection_1_id": 1,
"collection_3_id": 12
},
{
"_id": 6,
"name": "collection 2:6",
"collection_1_id": 1,
"collection_3_id": 13
},
{
"_id": 8,
"name": "collection 2:8",
"collection_1_id": 2,
"collection_3_id": 14
},
{
"_id": 9,
"name": "collection 2:9",
"collection_1_id": 2,
"collection_3_id": 15
},
]
Collection 3:
[
{
"_id": 12,
"name": "collection 3:12"
},
{
"_id": 13,
"name": "collection 3:13"
},
{
"_id": 14,
"name": "collection 3:14"
},
{
"_id": 15,
"name": "collection 3:15"
}
]
What I want out of it is:
[
{
"_id": 1,
"name": "Collection 1:1",
"collection_2_documents": [
{
"_id": 5,
"name": "collection 2:5",
"collection_1_id": 1,
"collection_3_id": 12,
"collection_3_document": {
"_id": 12,
"name": "collection 3:12"
}
},
{
"_id": 6,
"name": "collection 2:6",
"collection_1_id": 1,
"collection_3_id": 13,
"collection_3_document": {
"_id": 12,
"name": "collection 3:12"
}
}
]
},
{
"_id": 2,
"name": "Collection 1:2",
"collection_2_documents": [
{
"_id": 8,
"name": "collection 2:8",
"collection_1_id": 2,
"collection_3_id": 14,
"collection_3_document": {
"_id": 14,
"name": "collection 3:14"
}
},
{
"_id": 9,
"name": "collection 2:9",
"collection_1_id": 2,
"collection_3_id": 15,
"collection_3_document": {
"_id": 15,
"name": "collection 3:15"
}
}
]
}
]
This is the current aggregate/lookup that I have which returns separate arrays of documents from collections 2 and 3.
[
{
$lookup: {
from: 'Collection 2',
localField: 'collection_2_ids',
foreignField: '_id',
as: 'collection_2_documents'
}
},
{
$lookup: {
from: 'Collection 3',
localField: 'collection_2_documents.collection_3_id',
foreignField: '_id',
as: 'collection_3_document'
}
}
]
CodePudding user response：
You have to make use of pipeline option of $lookup stage and use nested $lookup on collection3 inside collection2's lookup
db.collection1.aggregate([
{
"$lookup": {
"from": "collection2",
"let": {
"sourceCollection_2_ids": "$collection_2_ids"
},
"pipeline": [
{
"$match": {
"$expr": {
"$in": [
"$_id",
"$$sourceCollection_2_ids"
]
}
}
},
{
"$lookup": {
"from": "collection3",
"let": {
"sourceCollection_3_id": "$collection_3_id"
},
"pipeline": [
{
"$match": {
"$expr": {
"$eq": [
"$_id",
"$$sourceCollection_3_id"
]
},
}
},
],
"as": "collection_3_document"
}
},
{
"$set": {
"collection_3_document": {
"$arrayElemAt": [
"$collection_3_document",
0
]
}
}
},
],
"as": "collection_2_documents"
}
},
])