I’m inserting these documents:
db.users.insert({user1: 'a',user2: 'b'});
db.users.insert({user1: 'b',user2: 'a'});
db.users.insert({user1: 'c',user2: 'd'});
db.users.insert({user1: 'd',user2: 'c'});
Please note that user1 and user2 are actually ObjectId, but I’m writing them as single char to better explain the issue.
I need to select unique rows, where unicity is given by the couple, i.e. two users are connected to each other.
So in the inserts above Im creating 2 unique couples/pairs:
user 'A' paired with user 'B'
user 'C' paired with user 'D'
Is it possible to accomplish this directly in mongo or do I need some algorithm in my code (javascript)?
The final result should be
[
{user1: 'A',user2: 'B'}
,{user1: 'C',user2: 'D'}
]
This would also work:
[
{user1: 'B',user2: 'A'}
,{user1: 'D',user2: 'C'}
]
2
Answers
You can use the aggregation pipeline. It groups documents by pair of users and uses
$cond
to ensure that the order of the users doesn’t matter.https://mongoplayground.net/p/8FLUiMDkG85
Without much consideration for special case like only 1 directed relation / more than 2 directed relations(i.e. expected always 2 relations for certain couple). You can simply
$group
by$setUnion
of 2 users and pick the first doc.Mongo Playground