CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
tazmin32w
tazmin32w

Reputation: 157

MongoDB conditional $lookup with aggregration framework

I'm trying to do a conditional lookup with aggregration framework. In my local and foreign collections I have two fields: fieldA and fieldB. If fieldA != 0 my $lookup should be:

{ from: 'collectionA', localField: 'fieldA', foreignField: 'fieldA', as: 'agg' }

Otherwise, if fieldA = 0 my $lookup should be:

{ from: 'collectionA', localField: 'fieldB', foreignField: 'fieldB', as: 'agg' }

Is it possible to combine these conditions with a single $lookup?

Upvotes: 1

Views: 336

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22316

It's not really possible OOB, but you can work around this in multiple ways.

for example add a new "temporary" field based on this condition:

db.colleciton.aggregate([
    {
        $lookup: {
            from: 'collectionA',
            let: {
                fieldA: "$fieldA", fieldB: "$fieldB"
            },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $eq: [
                                {
                                    $cond: [
                                        {
                                            $eq: [
                                                '$$fieldA',
                                                0,
                                            ],
                                        },
                                        '$$fieldB',
                                        '$$fieldA',
                                    ],
                                },
                                {
                                    $cond: [
                                        {
                                            $eq: [
                                                '$$fieldA',
                                                0,
                                            ],
                                        },
                                        '$fieldB',
                                        '$fieldA',
                                    ],
                                }
                            ],
                        },
                    },
                },
            ],
            as: 'agg',
        },
    }
])

The issue with this approach is that indexes won't be utilized for the lookup for older Mongo versions, which in some cases can be crucial.

You can work around for performance purposes like so:

db.collection.aggregate([
    {
        $facet: {
            one: [
                {
                    $match: {
                        fieldA: { $ne: 0 },
                    },
                },
                {
                    $lookup: { from: 'collectionA', localField: 'fieldA', foreignField: 'fieldA', as: 'agg' },
                },
                {
                    $match: {
                        'agg.0': { $exists: true },
                    },
                },
            ],
            two: [
                {
                    $match: {
                        fieldA: { $eq: 0 },
                    },
                },
                {
                    $lookup: { from: 'collectionA', localField: 'fieldB', foreignField: 'fieldB', as: 'agg' },
                },
                {
                    $match: {
                        'agg.0': { $exists: true },
                    },
                },
            ],
        },
    },
    {
        $addFieldS: {
            combined: {
                $concatArrays: [
                    '$one',
                    '$two',
                ],
            },
        },
    },
    {
        $unwind: '$combined',
    },
    {
        $replaceRoot: {
            newRoot: "$combined"
        },
    },
]);

While there is some overhead here it will still work faster than an unindexed lookup.

Upvotes: 1

Related Questions