skip to Main Content

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


  1. 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.

    db.users.aggregate([
      {
        $group: {
          _id: {
            $cond: [
              { $gt: [ "$user1", "$user2" ] },
              { user1: "$user1", user2: "$user2" },
              { user1: "$user2", user2: "$user1" }
            ]
          },
          count: { $sum: 1 }
        }
      },
      {
        $match: { count: { $gt: 1 } }
      },
      {
        $project: {
          _id: 0,
          user1: "$_id.user1",
          user2: "$_id.user2"
        }
      }
    ])
    

    https://mongoplayground.net/p/8FLUiMDkG85

    Login or Signup to reply.
  2. 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.

    db.collection.aggregate([
      {
        $group: {
          _id: {
            "$setUnion": [
              [
                "$user1"
              ],
              [
                "$user2"
              ]
            ]
          },
          doc: {
            $first: "$$ROOT"
          }
        }
      }
    ])
    

    Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search