Filter based on values after join operation - MongoDB

Question

I have two collections in the following format -

collection1:

{
    "_id": "col1id1",
    "name": "col1doc1",
    "properties": [ "<_id1>", "<_id2>", "<_id3>"]
}

collection2:

{
    "_id": "<_id1>",
    "name": "doc1",
    "boolean_field": false
},
{
    "_id": "<_id2>",
    "name": "doc2",
    "boolean_field": true
},
{
    "_id": "<_id3>",
    "name": "doc3",
    "boolean_field" : false
}

The desired output is:

{
    "_id": "col1id1",
    "name": "col1doc1",
    "property_names": ["doc1", "doc3"]
}

The field properties of document in collection1 has three IDs of documents in collection2 but the output after join operation should contain only those which have the boolean_field value as false.

How can I perform this filter with a join operation in MongoDB?

Answer 1

In MongoDB 3.6+ you can add a pipeline to your $lookup stage to include additional join conditions.

A solution was less obvious than I expected since your source lookup field is an array, but the following should return the expected outcome:

db.collection1.aggregate([
    { $match: {
        _id: "col1id1"
    }},

    // Filtered lookup based on agg pipeline
    { $lookup: {
        from: "collection2",
        // let references fields in the original collection
        let: { property: "$properties" },
        pipeline: [
            { $match:
                // use $expr to compare fields using let variables
                { $expr:
                    { $and: [
                        { $in: [ "$_id", "$$property" ] },
                        { $eq: [ "$boolean_field", false ] }
                    ]}
                }
            },
            { $project: { "_id": 0, "name": 1 }}
        ],
        as: "property_names"
    }},

    // Project desired output fields 
    { $project: {
        "name" : "$name",
        "property_names": {
            $map: {
                input: "$property_names", 
                in: "$$this.name"
            }
    }
    }
}
]).pretty()

Sample output:

{
    "_id" : "col1id1",
    "name" : "col1doc1",
    "property_names" : [
        "doc1",
        "doc3"
    ]
}