Accumulator $addToSet not implemented yet. In Cosmos MongoDB

Question

i have migrated my database that was on mongodb local server. So i have migrated it to Azure Cosmos DB mongodb cluster. previously i have written aggregate queries on mongodb compass on my local database but now on this cosmosdb version, many aggregate queries functions are not working can you somehow help convert my queries from mongodb compass aggregate pipeline to cosmos db pipeline by also explaining a little bit.

Aggregate Pipeline

[
  {
    $match: {
      "info.dates": "2017-01-13",
      "innings.team": "Pakistan",
    },
  },
  {
    $unwind: "$innings",
  },
  {
    $unwind: "$innings.overs",
  },
  {
    $unwind: "$innings.overs.deliveries",
  },
  {
    $match: {
      "innings.overs.deliveries.bowler": {
        $regex: /.*/,
      },
    },
  },
  {
    $group: {
      _id: "$innings.overs.deliveries.bowler",
      totalOvers: {
        $addToSet: "$innings.overs.over", // Using $addToSet to ensure unique overs
      },
      totalRuns: {
        $sum: "$innings.overs.deliveries.runs.total",
      },
      totalWickets: {
        $addToSet:
          "$innings.overs.deliveries.wickets",
      },
    },
  },
  {
    $project: {
      _id: 1,
      totalOvers: {
        $size: "$totalOvers", // Get the size of the array to get the total unique overs
      },
      totalRuns: 1,
      totalWickets: {
        $size: "$totalWickets",
      },
      economy: {
        $divide: [
          "$totalRuns",
          {
            $size: "$totalOvers",
          },
        ],
      },
    },
  },
]

Document Structure (attaching file link to demonstrate)

https://pern-my.sharepoint.com/:u:/g/personal/20-arid-507_student_uaar_edu_pk/ERvJmaIg4glBqT4m27SJGPABXZeK1nzuc0hqrPaT56Ul-w?e=UekBZt

Answer 1

Hi ,

In Azure Cosmos DB's MongoDB API, some MongoDB Compass aggregate query features may differ. Convert your query to work with Cosmos DB by replacing $addToSet with $push for arrays, as Cosmos DB doesn't natively support $addToSet. Also, replace $regex with $regexMatch for regex queries. In your provided query, the $addToSet for totalWickets is unnecessary; use $sum directly. Lastly, Cosmos DB may not support $size in all cases, so use $arrayElemAt with $size and $arrayToObject to emulate it. Adapt the query accordingly, considering Cosmos DB's syntax and limitations for a seamless transition. Regards, Saravanan Ganesan.