Need help for $lookup $nin mongodb

Question

I have 2 collections in Mongodb product and review.

product collection contains:

{ 
    "_id" : ObjectId("604d15830d879652dad55e23"), 
    "productId" : "0001"
}
{ 
    "_id" : ObjectId("604d15b79bc9dd5940f5ff28"), 
    "productId" : "0002"
}
{ 
    "_id" : ObjectId("604d15bd9bc9dd5940f5ff29"), 
    "productId" : "0003"
}
{ 
    "_id" : ObjectId("604d15c29bc9dd5940f5ff2a"), 
    "productId" : "0004"
}
{ 
    "_id" : ObjectId("604d186b9bc9dd5940f5ff2f"), 
    "productId" : "0005"
}
{ 
    "_id" : ObjectId("604d18719bc9dd5940f5ff30"), 
    "productId" : "0006"
}
{ 
    "_id" : ObjectId("604d18789bc9dd5940f5ff31"), 
    "productId" : "0007"
}
{ 
    "_id" : ObjectId("604d187d9bc9dd5940f5ff32"), 
    "productId" : "0008"
}
{ 
    "_id" : ObjectId("604d18829bc9dd5940f5ff33"), 
    "productId" : "0009"
}
{ 
    "_id" : ObjectId("604d18889bc9dd5940f5ff34"), 
    "productId" : "0010"
}

and the review collection:

{ 
    "_id" : ObjectId("604d15ce0d879652dad55e24"), 
    "userId" : "1", 
    "exclusion" : [
        "0002", 
        "0003"
    ]
}
{ 
    "_id" : ObjectId("604d185d9bc9dd5940f5ff2e"), 
    "userId" : "2", 
    "exclusion" : [
        "0001", 
        "0002", 
        "0003"
    ]
}
{ 
    "_id" : ObjectId("604d18df9bc9dd5940f5ff35"), 
    "userId" : "3", 
    "exclusion" : [
        "0001", 
        "0002", 
        "0003", 
        "0004", 
        "0005", 
        "0006", 
        "0007"
    ]
}
{ 
    "_id" : ObjectId("604d19379bc9dd5940f5ff36"), 
    "userId" : "4", 
    "exclusion" : [
        "0002", 
        "0003", 
        "0004", 
        "0007", 
        "0008", 
        "0009"
    ]
}

And I would like to show product who are not in the exclusion array of a specific user.

Example: if I am the user 4, I want to see product 0001, 0005, 0006 and 0010 only. I have tried many things like $lookup but it was not working.

user_5070460 · Answer

UPDATED based on comments.

If product collection is shareded, then try this:

db.product.aggregate([
    {
        $group: {
            _id: null,
            products: { $push: "$$ROOT" }
        }
    },
    {
        $lookup: {
            from: "review",
            let: { products: "$products" },
            pipeline: [
                {
                    $match: { userId: "4" }
                },
                {
                    $addFields: {
                        products: {
                            $filter: {
                                input: "$$products",
                                as: "product",
                                cond: {
                                    $not: {
                                        $in: ["$$product.productId", "$exclusion"]
                                    }
                                }
                            }
                        }
                    }
                }
            ],
            as: "reviews"
        }
    },
    {
        $replaceRoot: {
            newRoot: { $first: "$reviews" }
        }
    }
]);

Output:

{
    "_id" : ObjectId("604d19379bc9dd5940f5ff36"),
    "userId" : "4",
    "exclusion" : [
        "0002",
        "0003",
        "0004",
        "0007",
        "0008",
        "0009"
    ],
    "products" : [
        {
            "_id" : ObjectId("604d15830d879652dad55e23"),
            "productId" : "0001"
        },
        {
            "_id" : ObjectId("604d186b9bc9dd5940f5ff2f"),
            "productId" : "0005"
        },
        {
            "_id" : ObjectId("604d18719bc9dd5940f5ff30"),
            "productId" : "0006"
        },
        {
            "_id" : ObjectId("604d18889bc9dd5940f5ff34"),
            "productId" : "0010"
        }
    ]
}

Need help for $lookup $nin mongodb

1 Answers