$lookup pipeline with $eq on subdocument

[Nefiga]

Hello,

I'm having problems using $match in the $lookup stage to get the desired result. Consider the following records:

db.child.insert({ "backup": "red", "transactions" : [ { "color" : "red" } ] });

db.child.insert({ "backup": "red", "transactions" : [ { "color" : "red" }, { "color" : "blue" } ] });

db.child.insert({ "backup": "blue", "transactions" : [ { "color" : "blue" } ] });

db.parent.insert({ "primary" : "red" });

This query produces the desired result:

db.parent.aggregate([{$lookup: {

  from: 'child',

  'localField': 'primary',

  'foreignField': 'transactions.color',

  as: 'childLookup'

}}]).pretty();

This query does not. For some reason, the $eq aggregation operator does not seem to like subdocument syntax. I know there are "ways" to get this working by using unwind, etc., but I'm trying for a performant result that would be utilizing an index on transactions.color. I know the problem is with the way I am using the $eq operator. The reason I am doing it this way is that I want to add other stages into the pipeline (e.g. a $project to filter out the transactions subdocument that doesn't match).

db.parent.aggregate([{$lookup: {

  from: 'child',

  let: {'primaryColor': '$primary'},

  pipeline: [

    { $match:

      { $expr:

        { $and:

          [

            { $eq: ["$transactions.color", "$$primaryColor"] }

          ]

        }

      }

    }

  ],

  as: 'childLlookup'

}}]).pretty();

Any help with the $eq syntax?

[Wan Bachtiar]

For some reason, the $eq aggregation operator does not seem to like subdocument syntax

Hi Nefiga,

The reason here is that transactions field is an array of subdocuments, not just a subdocument. If you were to apply $unwind stage first to transform into subdocuments, it should work. In aggregation pipeline $eq is a strict equality only, and it does not reach into arrays.

If transactions.color is an array, the $expr you want to use would be $in. For example:

db.parent.aggregate([
    {"$lookup": {
        from: 'child',
        let: {'primaryColor': '$primary'

},
        pipeline: [
            { $match:
                { $expr:
                    { $in: ["$$primaryColor", "$transactions.color"] }
                }
            }
        ],
        as: 'childLookup'
        }
    }
])

I’m trying for a performant result that would be utilizing an index on transactions.color

As documented in $expr behaviour, $expr currently (as of v4.0) does not support multikey indexes.

Regards,
Wan.

​

[Nefiga]

Thanks for the detailed reply. I appreciate you sharing your knowledge with me. I did not realize that note about $expr, even though it was on its own standalone paragraph in the documentation :)

Sincerely,

Ben