CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
IUnknown
IUnknown

Reputation: 9809

Difference operation on collection

We have the following collections:

requests:

    {  "requestid" : 'R1', "stocks" : [{"scripid" : "xxxxx1" }, {"scripid" : "xxxxx2" },{"scripid" : "xxxxx3" },{"scripid" : "xxxxx4" }] }
    {  "requestid" : 'R2', "stocks" : [{"scripid" : "xxxxx0" }] }

stocks:

{"scripid" : "xxxxx2" }
{"scripid" : "xxxxx3" }
{"scripid" : "..." }

We would like to diff request's scrips with stocks collection - and return requests(and scrips) which are not a part of stocks collection.
So the results of mongo operation will be: 

 {  "requestid" : 'R1', "stocks" : [{"scripid" : "xxxxx1" }, {"scripid" : "xxxxx4" }] }
 {  "requestid" : 'R2', "stocks" : [{"scripid" : "xxxxx0" }] }

How can this be achieved in the most efficient manner.
We are using java driver 3.7.xxx

Upvotes: 0

Views: 97

Answers (1)

Ptijohn
Ptijohn

Reputation: 231

I believe this does the trick:

db.requests.aggregate([{
    $lookup: {
        from: "stocks",
        localField: "stocks.scriptid",
        foreignField: "scriptid",
        as: "matched_stocks"
    }
}, {
    $project: {
        "matched_stocks._id": 0
    }
}, {
    $project: {
        request_id: 1,
        stocks: {
            $setDifference: ["$stocks", "$matched_stocks"]
        }
    }
}])

Edit:

As suggested by @MrS.Sharma below, you can use Aggregates.project(Projections.computed("stocks", Document.parse("{ $setDifference: ['$stocks', '$matched_stocks'] }"))) to use $setDifference.

Edit 2:

I guess this is then the actual full answer:

AggregateIterable<Document> documents =
    requests.aggregate(
        Arrays.asList(
            Aggregates.lookup("stocks", "stocks.scrip", "scrip", "matched_stocks"),
            Aggregates.project(Projections.exclude("matched_stocks._id")),
            Aggregates.project(
                Projections.fields(
                    Projections.include("requestid"),
                    Projections.computed(
                        "stocks",
                        Document.parse(
                            "{ $setDifference: ['$stocks', '$matched_stocks'] }"))))));

Upvotes: 3

Related Questions