Question 252473 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – Perform aggregation to eliminate duplicates and get unique counts

I have mongo documents like below, want to perform aggregation to find the errorcodes count for each unique contacts.

{
  "_id": {
    "$oid": ""
  }, 
  "campId": "61baef7817cd8ff66518", //camp1
  "contactId": "61aa6fbf77490b0007714273", // contact 1
  "title": "Happy Holidays!",
  "communicationType": "EMAIL", 
  "contactedOnTime": {
    "$numberLong": "1695182032" // AT TIME1
  },  
  "communicationValidationError": "EMAIL_ADDRESS_NOT_PRESENT"
}

{
    "_id": {
        "$oid": ""
    },
    "campId": "61baef7817cd8ff66518", //camp1
    "contactId": "61aa6fbf77490b0007714273", // contact1
    "title": "Happy Holidays!",
    "communicationType": "EMAIL",  
    "contactedOnTime": {
        "$numberLong": "1695182074" // AT TIME2 
    },
    "communicationValidationError": "EMAIL_ADDRESS_NOT_PRESENT"
}
{
    "_id": {
        "$oid": ""
    },
    "campId": "61baef7817cd8ff66518", // camp1
    "contactId": "61aa6fbf77490b0007714274", // contact2
    "title": "Happy Holidays!",
    "communicationType": "EMAIL",
    "contactedOnTime": {
        "$numberLong": "1695182059" 
    },
    "communicationValidationError": "EMAIL_BOUNCED"
}

I have tried below, however this doesn’t eliminate the duplicate contacts for a camp and shows count as 2 instead of 1. I need to pick latest communicationValidationError for unique contacts and get it as totalcounts of communicationValidationError of a campId.

db.myCollection.aggregate([
    { $project: { _id: 0, campId: 1, contactId: 1, communicationValidationError: 1 } },
    { $sort: { "contactedOnTime.numberLong":-1}},
    { $match: { campId: '61baef7817cd8ff66518' } },
    { $group: { _id: { communicationValidationError: '$communicationValidationError' }, totalErrors: { $sum: 1 } } }
]).pretty()
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Though the above answer by @Yong Shun worked, i had to achieve same without using $setWindowFields: as i wasn't able to frame it in java spring-boot. Here is document showing that the spring supports this operation, however i wasn't able to! Maybe the spring-version i was using not supporting this.

    So, i tried to re-write the query using group stages. I have put it below for somebody who is facing same issue as mine,

    db.collection.aggregate([
  {
    $sort: {
      contactedOnTime: -1
    }
  },
  {
    $match: {
      campId: "61baef7817cd8ff66518"
    }
  },
  {
    $group: {
      _id: {
        contactId: "$contactId"
      },
      doc: {
        "$first": "$$ROOT"
      }
    }
  },
  {
    "$unset": "_id"
  },
  {
    "$replaceRoot": {
      "newRoot": "$doc"
    }
  },
  {
    "$group": {
      _id: "$errorCode",
      totalErrors: {
        "$sum": 1
      }
    }
  }
])

  2. 0

    Main point:

    Pick latest communicationValidationError for unique contacts and get it as totalcounts of communicationValidationError of a campId.

    Thus, the query should be:

    1. $match

    2. $setWindowFields – Add new field rankInLatest. This will group the documents by contactId with the ranking by contactedOnTime descending.

    3. $match – Filter the document with rankInLatest: 1. This will return each document of contactId with the latest contactedOnTime.

    4. $group – Group by communicationValidationError and perform the count.

    db.collection.aggregate([
  {
    $match: {
      campId: "61baef7817cd8ff66518"
    }
  },
  {
    $setWindowFields: {
      partitionBy: "$contactId",
      sortBy: {
        contactedOnTime: -1
      },
      output: {
        rankInLatest: {
          $rank: {}
        }
      }
    }
  },
  {
    $match: {
      rankInLatest: 1
    }
  },
  {
    $group: {
      _id: {
        communicationValidationError: "$communicationValidationError"
      },
      totalErrors: {
        $sum: 1
      }
    }
  }
])

    Demo @ Mongo Playground

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search