how to get the count of documents with value ​in different fields in mongo

Question

I have some documents as the following

  {
    _id: 1,
    lender_id: 10,
    borrower_id: 20,
    message: 'Message from lender'
  },
  {
    _id: 2,
    lender_id: 20,
    borrower_id: 10,
    message: 'Response to lender'
  }
  {
    _id: 3,
    lender_id: 10,
    borrower_id: 30,
    message: 'Message to other user'
  }

I want to get the count of the documents in this particular case when the lender_id field or borrower_id is equal to 10.

I try with this:

    chats = await Chat.aggregate([
        {
            $match: { $or: [
                {lender_id: 10},
                {borrower_id: 10}
            ]}
        },
        {
            $group: {
                _id: null,
                differentChats: {$sum: 1}
            }
        },
    ])

    // Response: [ { _id: null, differentChats: 3 } ] --- must be 2

How i can get the total count of unique chats. thx for read.

Answer 1

We need create a Set of unique lender_id and borrower_id like shown below (by changing the order). In the last stage, we take the Set size.

chats = await Chat.aggregate([
  {
    $match: {
      $or: [ {lender_id: 10}, {borrower_id: 10} ]
    }
  },
  {
    $group: {
      _id: null,
      differentChats: {
        $addToSet: {
          $cond: [
            { $gt: ["$lender_id", "$borrower_id"]},
            [ "$lender_id", "$borrower_id" ],
            [ "$borrower_id", "$lender_id" ]
          ]
        }
      }
    }
  },
  {
    $project: {
      differentChats: { $size: "$differentChats" }
    }
  }
])

MongoPlayground