I have 2 collections (users and tenants). Simplified, the user looks like this:
{
"_id": ObjectId("..."),
"associatedTenants": [{
"tenantId": ObjectId("A"),
"role": "foo"
}, {
"tenantId": ObjectId("B"),
"role": "bar"
}]
}
Simplified tenant model looks like this:
{
"_id": ObjectId("A"),
"name": "Lorem"
}
And I’d need to query a user with aggregated tenants, so ideally add a new field tenant
next to the tenantId
with the actual tenant document, like this:
{
"_id": ObjectId("..."),
"associatedTenants": [{
"tenantId": ObjectId("A"),
"role": "foo",
"tenant": {
"name": "Lorem"
}
}, {
"tenantId": ObjectId("B"),
"role": "bar",
"tenant": {
"name": "Ipsum"
}
}]
}
I know it can be done - it seems so simple, but I’ve been banging my head agains the wall because of it
So far I’ve tried various combinations of $lookup
, $project
with $map
, $mergeObject
with $arrayElemAt
, but nothing seems to cut it… The closest answer I cloud find was this: https://stackoverflow.com/questions/60342985, but I just don’t seem to be able to adapt it. Can anyone see a way, please?
Btw. (a bit unrelated) - if I $lookup
the tenants and save them into a new field:
$lookup: {
from: "tenants",
localField: "associatedTenants.tenantId",
foreignField: "_id",
as: "aggregatedAssociatedTenants"
}
The order of aggregatedAssociatedTenants
is different than the order of associatedTenants
. Doesn’t $lookup
preserve the order of the original array?