Find users who have not invited a user

Question

I want to find those users who have not invited a single user for a single request (using the 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 ddd invited

3, but ccc and ddd the user did not invite anyone

so I want pic ccc and ddd users

it would be better if possible using the aggregate, because I need to perform some operation on the selected data.

Answer 1

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

Users who did not send an invitation are those who have an empty invitation array. To get only those users, simply filter the documents at the $match stage using $exists and the numerical indexing of the array.

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

which gives:

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

Answer 2

There is another way to do the same. But this method may be less effective than @Styvane's answer. This should work if there are smaller elements.

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

If other information is needed, it can also be obtained (sorry, but here it gets dirty)

 [ {$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}} ]