Select distinct combination of two fields with a condition in MongoDB (Node JS)-CodePudding

I have this kind of collection named 'Message'

[
  {
    _id: new ObjectId("62641aea1fbe19349f8fba78"),
    text: 'Hello',
    user: new ObjectId("625b8f00e2464fb758263b4d"),
    receiver: new ObjectId("62638d3e5bcd98e7e48ca1b7"),
    createdAt: 2022-04-23T15:27:38.270Z,
    updatedAt: 2022-04-23T15:27:38.270Z,
    __v: 0
  },
  {
    _id: new ObjectId("6264256fc0ee5093f8d994a0"),
    text: 'Hi',
    user: new ObjectId("62638f1495b841266161b032"),
    receiver: new ObjectId("62638d3e5bcd98e7e48ca1b7"),
    createdAt: 2022-04-23T16:12:31.155Z,
    updatedAt: 2022-04-23T16:12:31.155Z,
    __v: 0
  },
  {
    _id: new ObjectId("62642dc05318104caabbdecc"),
    text: 'Hi',
    user: new ObjectId("625b8f00e2464fb758263b4d"),
    receiver: new ObjectId("62638d3e5bcd98e7e48ca1b7"),
    createdAt: 2022-04-23T16:48:00.416Z,
    updatedAt: 2022-04-23T16:48:00.416Z,
    __v: 0
  }
]

here user is the sender and the other one receiver. If my user id is "myId", I want to retrieve distinct combinations of user and receiver of my message objects which means with a condition of user == myID or receiver == myId.

Expected values.

[
  {
    user: new ObjectId("625b8f00e2464fb758263b4d"),
    receiver: new ObjectId("62638d3e5bcd98e7e48ca1b7")
  },
  {
    user: new ObjectId("62638f1495b841266161b032"),
    receiver: new ObjectId("62638d3e5bcd98e7e48ca1b7")
  }
]

So I'm new to MongoDB, is there any possible way to do that? or is there anything I have missed?

CodePudding user response：

Welcome Sandeep Vithanage, You can do something like this:

db.collection.aggregate([
  {
    $match: {
      $or: [
        {
          user: myId
        },
        {
          receiver: myId
        }
      ]
    }
  },
  {
    $group: {
      "_id": {
        user: "$user",
        receiver: "$receiver"
      }
    }
  },
  {
    $replaceRoot: {
      "newRoot": "$_id"
    }
  }
])

As you can see working on the playground. This is an aggregation pipeline. The first step is to match the cases you asked for. The second one is to group them by combinations of users and receivers. The third one is just formatting.

CodePudding user response：

using $match and $project like below:

await Model.aggregate([{ 
     $match: { 
       $or: [
         {user: 'myid'},
         {receiver: 'myid'}
       ] } 
     },
     $project: {user: 1, receiver: 1}
])