So I have 2 collections (tenants
and campaigns
) and I'm trying to compose a query to return 1 tenant and 1 campaign. As an input, there is a tenant domain
and campaign slug
. Since I first need the tenant _id
to query the campaign (based on both tenantId
and slug
), aggregation seems more performative option (than making 2 consecutive queries).
Technically speaking, I know how to do that:
[{
$match: { 'domains.name': '<DOMAIN_HERE>' },
}, {
$lookup: {
from: 'campaigns',
localField: '_id',
foreignField: 'tenantId',
as: 'campaign',
pipeline: [{
$match: { slug: '<SLUG_HERE>' },
}],
},
}]
which returns:
{
_id: ObjectId('...'),
campaign: [{
_id: ObjectId('...'),
}],
}
But it feels very uncomfortable, because for one the campaign is returned as a field of tenant and for other the campaign is returned as a single item in an array. I know, I can process and better format the result programmatically afterwards. But is there any way to „hack“ the aggregation to achieve a result that looks more like this?
{
tenant: {
_id: ObjectId('...'),
},
campaign: {
_id: ObjectId('...'),
},
}
This is just a simplified example, in reality this aggregation query is a bit more complicated (across more collections, upon few of which I need to perform a very similar query), so it's not just about this one simple query. So the ability to return an aggregated document as a separate object, rather than an array field on parent document would be quite helpful - if not, the world won't fall apart :)
CodePudding user response:
To all those whom it may concern...
Thanks to answers from some good samaritans here, I've figured it out as a combination of $addFields
, $project
and $unwind
. Extending my original aggregation query, the final pipeline would look like this:
[{
$match: { 'domains.name': '<DOMAIN_HERE>' },
}, {
$addFields: { tenant: '$$ROOT' },
}, {
$project: { _id: 0, tenant: 1 },
}, {
$lookup: {
from: 'campaigns',
localField: 'tenant._id',
foreignField: 'tenantId',
as: 'campaign',
pipeline: [{
$match: { slug: '<SLUG_HERE>' },
}],
},
}, {
$unwind: {
path: '$campaign',
preserveNullAndEmptyArrays: true,
},
}]
Thanks for the help!