Home > front end >  MongoDB aggregate - return as separate objects
MongoDB aggregate - return as separate objects

Time:11-12

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!

  • Related