Aggregation subquery , search inside list of a specific _id

Question

Ater many lectures i canot say if its possible to perform this sql equivalent query in 1 query with mongo :
SELECT * from collection WHERE _id NOT IN (SELECT blacklist from collection WHERE _id = 1 ) I tried many thing with aggregatio but didn't manage to work.

Here is my collection :

{
    "_id" : 1,
    "blacklist" : [8,9,10,3]
    "code_postal" : 67110,
    "loc" : {
        "type" : "Point",
        "coordinates" : [ 
            7.72, 
            48.91
        ]
    }
}

{
    "_id" : 2,
 "blacklist" : [18,1,93]
    "code_postal" : 67110,
    "loc" : {
        "type" : "Point",
        "coordinates" : [ 
            7.63, 
            48.91
        ]
    }
}

{
    "_id" : 3,
     "blacklist" : [7,3]
    "code_postal" : 67110,
    "loc" : {
        "type" : "Point",
        "coordinates" : [ 
            7.7, 
            48.96
        ]
    }
}

Result expected with this query and this collection should be (_id 3 excluded because is in the blacklist of _id 1):

{
        "_id" : 1,
        "blacklist" : [8,9,10,3]
        "code_postal" : 67110,
        "loc" : {
            "type" : "Point",
            "coordinates" : [ 
                7.72, 
                48.91
            ]
        }
    }

    {
            "_id" : 2,
         "blacklist" : [18,1,93]
            "code_postal" : 67110,
            "loc" : {
                "type" : "Point",
                "coordinates" : [ 
                    7.63, 
                    48.91
                ]
            }
        }

regards

Answer 1

I think you should save yourself the trouble and just use two queries (first, get the blacklist, then query documents) but if there's really no other way:

db.so.aggregate([
{
    // First, choose what fields to return (less is better)
    // and only return a certain document {_id: 1}'s blacklist.
    $project: {
        _id:         1,
        code_postal: 1,
        loc:         1,
        bl:          {
            // Only return blacklists if the parent has 
            // a certain ID.
            $cond: {
                if:   {$eq: ["$_id", 1]}, // or a different value
                then: "$blacklist",
                else: 0
            }
        }
    }
},

{
    // Group all documents in one, so that we can embed the
    // blacklist into all documents, not just in {_id:1}.
    $group: {
        _id:       null, // Group everything.
        items:     { $push: "$$ROOT" },
        blacklist: { $max: "$bl" } // "{}" is higher than "0".
                                   // This makes sure that we only
                                   // get one blacklist, not
                                   // [ [], 0, 0, 0, ... ]
    }
},

{
    // Pull the documents apart again.
    $unwind: "$items"
},

{
    $project: {
        _id:         "$items._id",
        code_postal: "$items.code_postal",
        loc:         "$items.loc",
        whitelisted: {
            // If everything in the following array is true,
            // then the _id is not in the blacklist.
            $allElementsTrue: [{
                $map: {
                    // Iterate over $blacklist
                    input: "$blacklist",
                    as:    "currentId",
                    in:    {
                        // If the ids don't match, return true.
                        $ne: ["$$currentId", "$items._id"]
                    }
                }
            }]
        }
    }
},

{
    // Only find non-blacklisted documents.
    $match: {
        "whitelisted": true
    }
}

]);

Note that because this groups all documents into one, you'll have to take care not to exceed Mongo's document size limits.

This produces the following:

[ 
    {
        "_id" : 1,
        "code_postal" : 67110,
        "loc" : {
            "type" : "Point",
            "coordinates" : [ 
                7.72, 
                48.91
            ]
        },
        "whitelisted" : true
    }, 
    {
        "_id" : 2,
        "code_postal" : 67110,
        "loc" : {
            "type" : "Point",
            "coordinates" : [ 
                7.63, 
                48.91
            ]
        },
        "whitelisted" : true
    }
]