shaishab roy shaishab roy - 22 days ago 6
Node.js Question

Find users who has not invited any user

I want to find those users who has not invited any user by a single query (using aggregate).

for Example : I have 4 users in DB

{
"_id" : ObjectId("581a18d41b6c5c752f11c87a"),
"name": "aaa",
"invitedBy": ObjectId("5808f53d28c14ee470856d8b")
},

{
"_id" : ObjectId("581a1a671b6c5c752f11c87b"),
"name": "bbb",
"invitedBy": ObjectId("581a18d41b6c5c752f11c87a")
},

{
"_id" : ObjectId("581a1a671b6c5c752f11c87c"),
"name": "ccc",
"invitedBy": ObjectId("581a18d41b6c5c752f11c87a"),
},

{
"_id" : ObjectId("581a1a671b6c5c752f11c87d"),
"name": "ddd",
"invitedBy": ObjectId("581a1a671b6c5c752f11c87b"),
}


Here


1- aaa invited bbb and ccc user

2- bbb invited ddd user

3- but ccc and ddd user not invited any one


so I want to pic ccc and ddd users

it would be better if can by using aggregate because I need perform some operation on selected data.

Answer

You can use the $lookup operator to perform a left join to the collection itself.

The users who have not sent any invitation are those with empty invitation array. To retrieve only those users, simply filter the documents in a $match stage using the $exists operator and numeric array indexing.

db.users.aggregate(
    [
        { "$lookup": { 
            "from": "users", 
            "localField": "_id", 
            "foreignField": "invitedBy", 
            "as": "invitation"
        }}, 
        { "$match": { "invitation.0": { "$exists": false } } }
    ]
)

which yields:

{
    "_id" : ObjectId("581a1a671b6c5c752f11c87c"),
    "name" : "ccc",
    "invitedBy" : ObjectId("581a18d41b6c5c752f11c87a"),
    "invitation" : [ ]
}
{
    "_id" : ObjectId("581a1a671b6c5c752f11c87d"),
    "name" : "ddd",
    "invitedBy" : ObjectId("581a1a671b6c5c752f11c87b"),
    "invitation" : [ ]
}