MongoDB conditional $lookup with aggregration framework-CodePudding

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?

CodePudding user response：

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.