Aggregation query

Working with Data
1

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: [] }
        }
    }
]
2

This is also being discussed at Redirecting...

3

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:  [ ]
        }
    }
]
4 Likes