How to check $setDifference in two array using mongo-query

Question

UserDetails

{
    "_id" : "5c23536f807caa1bec00e79b",
    "UID" : "1",
    "name" : "A",
},
{
    "_id" : "5c23536f807caa1bec00e78b",
    "UID" : "2",
    "name" : "B",
}

UserProducts

{
    "_id" : "5c23536f807caa1bec00e79c",
    "UPID" : "100",
    "UID" : "1"
},
{
    "_id" : "5c23536f807caa1bec00e79c",
    "UPID" : "200",
    "UID" : "2"
}

Groups

{
    "_id" : "5bb20d7556db6915846da55f",
    "members" : {
        "regularStudent" : [
            "200" // UPID
        ],
    }
}

Step 1

I have to take UID from UserDetails check with UserProducts then take UPID from UserProducts

Step 2

we have to check this UPID mapped to Groups collection or not ?. members.regularStudent we are mapped UPID

Step 3

Suppose UPID not mapped means i want to print the UPID from from UserProducts

I have tried but couldn't complete this, kindly help me out on this.

Expected Output:

["100"]

Note: Expected Output is ["100"] , because UserProducts having UPID 100 & 200 but Groups collection mapped only 200.

My Code

db.UserDetails.aggregate(
{
$lookup: {
    from: "UserProducts",
    localField: "UID",
    foreignField: "UID",
    as: "userProduct"
    }
},
{ $unwind: "$userProduct" },
{
    "$project": { "_id" : 0, "userProduct.UPID" : 1 }
},
{
    $group: {
        _id: null,
        userProductUPIDs: { $addToSet: "$userProduct.UPID" }
    }
}
) // returns [ "100", "200" ]

db.Groups.aggregate([
    {
        $unwind: "$members.regularStudent"
    },
    {
        $group: {
            _id: null,
            UPIDs: { $addToSet: "$members.regularStudent" }
        }
    }
]) // returns ["200"]

Now i want to check $setDifference of both array, so i had added below code but returning error like $userProductUPIDs is not defined

  db.Groups.aggregate([
    {
        $unwind: "$members.regularStudent"
    },
    {
        $group: {
            _id: null,
            UPIDs: { $addToSet: "$members.regularStudent" }
        }
    },
    {
        $project: {
            members: {
                $setDifference: [ $userProductUPIDs , "$members" ]
            },
            _id : 0
        }
    }
    ])

mickl · Accepted Answer · 2019-01-10T16:58:03.123

1

As this is a follow up to one of my previous answers I will try to fix your code. The bottom line is that you need two queries as you can't upgrade your database so the code should look like below:

var queryResult = db.UserDetails.aggregate(
{
$lookup: {
    from: "UserProducts",
    localField: "UID",
    foreignField: "UID",
    as: "userProduct"
    }
},
{ $unwind: "$userProduct" },
{
    "$project": { "_id" : 0, "userProduct.UPID" : 1 }
},
{
    $group: {
        _id: null,
        userProductUPIDs: { $addToSet: "$userProduct.UPID" }
    }
});

let userProductUPIDs = queryResult.toArray()[0].userProductUPIDs;

db.Groups.aggregate([
    {
        $unwind: "$members.regularStudent"
    },
    {
        $group: {
            _id: null,
            UPIDs: { $addToSet: "$members.regularStudent" }
        }
    },
    {
        $project: {
            members: {
                $setDifference: [ userProductUPIDs , "$UPIDs" ]
            },
            _id : 0
        }
    }
]) // should return 100

edited Jan 10 '19 at 16:58

answered Jan 10 '19 at 14:16

mickl

48,568
9
60
89

Answer returning null – Kanagaraj Sa Jan 10 '19 at 14:31
@KanagarajSa should be fine now – mickl Jan 10 '19 at 14:33
No, i used your code, it returning `{ "members" : null }` – Kanagaraj Sa Jan 10 '19 at 14:44
@KanagarajSa please make sure that you correctly pass the data between queries, here you can verify the last part: https://mongoplayground.net/p/tZZYWsmusAX – mickl Jan 10 '19 at 14:55
i had copy and pasted your full code and run on my machine, but not working it is working for you ? – Kanagaraj Sa Jan 10 '19 at 14:59
`Groups` aggregation `$setDifference` values i am giving static means it working suppose giving dynamic `queryResult.userProductUPIDs` not working – Kanagaraj Sa Jan 10 '19 at 15:07
First part i am trying print the result using `print(queryResult);` i am getting error Maximum call stack size exceeded – Kanagaraj Sa Jan 10 '19 at 15:12
@KanagarajSa now I have a chance to test that as I just got back from work, current version works fine in Mongo shell – mickl Jan 10 '19 at 16:58
your has little bit problem, suppose grops collection `members.regularStudent` is object means it in not working properly, can you plase update your code – Kanagaraj Sa Jan 14 '19 at 06:57
@KanagarajSa it's always recommended to open a new question since this changes entire sense of this one. BTW: can't you just update objects to be an array as you tried in your today's question. I wanted to answer it but it disappeared for some reason :) – mickl Jan 14 '19 at 07:24
I had posted new question, kindly [check here](https://stackoverflow.com/questions/54178838/how-to-setdifference-in-array-object-using-mongo-db) – Kanagaraj Sa Jan 14 '19 at 09:38
@KanagarajSa is there any chance to just update `groups` and have arrays only or you want to keep it like that ? – mickl Jan 14 '19 at 09:53
I want to keep like this only – Kanagaraj Sa Jan 14 '19 at 09:55
you are getting my question ? can you please update your answer ? – Kanagaraj Sa Jan 14 '19 at 10:09
@KanagarajSa just did that – mickl Jan 14 '19 at 12:35

How to check $setDifference in two array using mongo-query

1 Answers1

Linked