Find users who has not invited any user-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

There is another method to do the same. But this method might end up being less efficient than @Styvane's answer. This should work if there are lesser elements.

[
 {$group: {_id: null, senders: {$addToSet: '$invitedBy'}, everyone: {$addToSet: '$_id'}}},
 {$project: {_id: 0, res: {$setDifference: ['$everyone', '$senders']}}}
]

In case other information is needed, that can also be obtained (sorry but it becomes bit dirty here)

[
     {$group: {_id: null, senders: {$addToSet: '$invitedBy'}, everyone: {$addToSet: {id: '$_id', name: '$name'}}}},
     {$project: {_id: 0, everyone: 1, res: {$setDifference: ['$everyone.id', '$senders']}}},
     {$unwind: '$everyone'},
     {$unwind: '$res'},
     {$project: {flag: {$eq: ['$everyone.id', '$res']}, everyone: 1}},
     {$match: {flag: true}}
]