I have 3 collections in a MongoDB instance. I need to join the 3 collections using the lookup operation and I need the collections nested within their respective parent objects.
For example, I have the following mongo tables:
db.opera.insert([
{ "tlocal" : 1, "name" : "A" },
{ "tlocal" : 2, "name" : "B"}
])
db.orders.insert([
{ "olocal" : 1, "name" : "ABC", "tlocal": 1},
{ "olocal" : 2, "name" : "DEF", "tlocal": 1},
{ "olocal" : 3, "name" : "LMN", "tlocal": 2},
{ "olocal" : 4, "name" : "OPY", "tlocal": 2}
])
db.olives.insert([
{ "_id" : 1, "name" : "abc", "olocal": 1},
{ "_id" : 2, "name" : "def", "olocal": 1},
{ "_id" : 3, "name" : "xyz", "olocal": 2},
{ "_id" : 4, "name" : "opq", "olocal": 2},
{ "_id" : 5, "name" : "lmn", "olocal": 3},
{ "_id" : 6, "name" : "ghi", "olocal": 3},
{ "_id" : 7, "name" : "jkl", "olocal": 3},
{ "_id" : 8, "name" : "uvw", "olocal": 4}
])
Now performing a looking on the 3 collections, like this
db.getCollection('opera').aggregate([
{
$lookup: {
from: "orders",
localField: "tlocal",
foreignField: "tlocal",
as: "orders"
}
},
{
$lookup: {
from: "olives",
localField: "orders.olocal",
foreignField: "olocal",
as: "olives"
}
},
])
This result is:
{
"_id" : ObjectId("5ae258de2b13b1c79901defb"),
"tlocal" : 1.0,
"name" : "A",
"orders" : [
{
"_id" : ObjectId("5ae258e62b13b1c79901defd"),
"olocal" : 1.0,
"name" : "ABC",
"tlocal" : 1.0
},
{
"_id" : ObjectId("5ae258e62b13b1c79901defe"),
"olocal" : 2.0,
"name" : "DEF",
"tlocal" : 1.0
}
],
"olives" : [
{
"_id" : 1.0,
"name" : "abc",
"olocal" : 1.0
},
{
"_id" : 2.0,
"name" : "def",
"olocal" : 1.0
},
{
"_id" : 3.0,
"name" : "xyz",
"olocal" : 2.0
},
{
"_id" : 4.0,
"name" : "opq",
"olocal" : 2.0
}
]
}
{
"_id" : ObjectId("5ae258de2b13b1c79901defc"),
"tlocal" : 2.0,
"name" : "B",
"orders" : [
{
"_id" : ObjectId("5ae258e62b13b1c79901deff"),
"olocal" : 3.0,
"name" : "LMN",
"tlocal" : 2.0
},
{
"_id" : ObjectId("5ae258e62b13b1c79901df00"),
"olocal" : 4.0,
"name" : "OPY",
"tlocal" : 2.0
}
],
"olives" : [
{
"_id" : 5.0,
"name" : "lmn",
"olocal" : 3.0
},
{
"_id" : 6.0,
"name" : "ghi",
"olocal" : 3.0
},
{
"_id" : 7.0,
"name" : "jkl",
"olocal" : 3.0
},
{
"_id" : 8.0,
"name" : "uvw",
"olocal" : 4.0
}
]
}
So my question is, is there a way I can nest/group the 'olives' objects array with their respective 'order' objects arrays within the respected opera objects? Something like this:
{
"_id" : ObjectId("5ae258de2b13b1c79901defb"),
"tlocal" : 1.0,
"name" : "A",
"orders" : [ {
"_id" : ObjectId("5ae258e62b13b1c79901defd"),
"olocal" : 1.0,
"name" : "ABC",
"tlocal" : 1.0,
"olives" : [
{
"_id" : 1.0,
"name" : "abc",
"olocal" : 1.0
},
{
"_id" : 2.0,
"name" : "def",
"olocal" : 1.0
}
]
},
{
"_id" : ObjectId("5ae258e62b13b1c79901defe"),
"olocal" : 2.0,
"name" : "DEF",
"tlocal" : 1.0,
"olives" : [
{
"_id" : 3.0,
"name" : "xyz",
"olocal" : 2.0
},
{
"_id" : 4.0,
"name" : "opq",
"olocal" : 2.0
}
]
}
]
}
{
"_id" : ObjectId("5ae258de2b13b1c79901defc"),
"tlocal" : 2.0,
"name" : "B",
"orders" : [
{
"_id" : ObjectId("5ae258e62b13b1c79901deff"),
"olocal" : 3.0,
"name" : "LMN",
"tlocal" : 2.0,
"olives" : [
{
"_id" : 5.0,
"name" : "lmn",
"olocal" : 3.0
},
{
"_id" : 6.0,
"name" : "ghi",
"olocal" : 3.0
},
{
"_id" : 7.0,
"name" : "jkl",
"olocal" : 3.0
}
]
},
{
"_id" : ObjectId("5ae258e62b13b1c79901df00"),
"olocal" : 4.0,
"name" : "OPY",
"tlocal" : 2.0,
"olives" : [
{
"_id" : 8.0,
"name" : "uvw",
"olocal" : 4.0
}
]
}
]
}
Thank you!