MongoDB.live, free & fully virtual. June 9th - 10th. Register Now MongoDB.live, free & fully virtual. June 9th - 10th. Register Now

Aggregation query

Hi all :slight_smile:

I have a question that I would like to get your help with.

I have users documents that have products array. Each product contains ProductID.
In addition, I have products documents.

I want to find all the users that have products that don’t exist in products document.

My query that doesn’t work:

db.users.aggregate([
    { 
        $lookup: 
        {
            from: "prodcuts", 
            localField: "Products.ProductID", 
            foreignField: "_id", 
            as:"resultTest"
        }
    },
    {
        $match:
        {
            eq: { resultTest: [] }
        }
    }
]

This is also being discussed at https://www.facebook.com/groups/mongodb.world/2818177351611713/

It’s strange that you’re discussing this on FB - not a place I think of for technical discussions. Anyway I happen to disagree with the solution that’s offered there.

Try this aggregation:

db.users.aggregate([
    { 
        $lookup: 
        {
            from: "products", 
            localField: "Products.ProductID",
            foreignField: "_id", 
            as:"resultTest"
        }
    },
    {$addFields:{
         diff:{$setDifference:[ "$Products.ProductID","$resultTest._id"]}
    }},
    {
        $match:
        {
            diff:  [ ]
        }
    }
]
3 Likes