How can we improve Azure Cosmos DB?

support COUNT of a query on a partitioned collection

db.collection.count() works just find but when attempting to count a filtered down collection db.collection.find({query...}).count() results in an Operation Failure that says 'query in command must target a single shard key' but need to be able to do counts across multiple partitions

147 votes
Vote
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
You have left! (?) (thinking…)
Mark shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

19 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...
  • Richa Garg commented  ·   ·  Flag as inappropriate

    Hi Azure team . I need to query count with filters . I see the issue 'query in command must target a single shard key' still exists . Can you please let me know if this is still work in progress ?

  • Sachin commented  ·   ·  Flag as inappropriate

    This is really needed. As a workaround since I am only interested in counts for investigative purposes against a DB, I use `db.vacancies.find().toArray().length` in a MongoShell/Robo3T

  • Daniel Vázquez Jiménez commented  ·   ·  Flag as inappropriate

    Currently I make this query with aggregation and consumes more than 30k RUS (50k documents) with match, order and count.

    This means that we constantly get many errors type: Request rate large.

    This feature is very necessary.

  • Abe commented  ·   ·  Flag as inappropriate

    This is a huge shortcoming that is preventing Cosmos from being a real player in the cloud database field. Please address it as quickly as possible.

  • alex commented  ·   ·  Flag as inappropriate

    This needs to get fixed.

    With the SQL API, you can Enable Cross Partition Queries.

    We either need this to work out of the box for the Mongo API or some way to override the restriction similar to the above flag.

  • Dewi Felstead commented  ·   ·  Flag as inappropriate

    You can achieve a count across partitions using the aggregation pipeline but is proves expensive in terms of RUs (roughly 13K RU to search a collection containing 30K documents using 1 filter condition) so we too would love to have a solution that avoids the use of the aggregation framework.

    db.collection.aggregate([{"$match": { fieldName: "fieldValue"}}, {"$count": "countOfDocuments"}])

  • Thiago Cabral commented  ·   ·  Flag as inappropriate

    I'm with the same problem. Apparently there is no way to update or aggregate if you do not use the shard key in the query.

  • Ignacy commented  ·   ·  Flag as inappropriate

    I'm also blocked by that, is there any way to execute query like: "db.collection.updateOne({"_id": ObjectId("abcdefgh1234")}, {"$set": {params}})". Seems to fail on doing find with query, whatever way of updating a record I choose.

  • Anonymous commented  ·   ·  Flag as inappropriate

    Do Azure Cosmos DB has a fix for this yet?

    As I am getting a similar error "com.mongodb.MongoCommandException: Command failed with error 61: 'query in command must target a single shard key' on server "while doing :

    //This line is erroring out
    long recordCount = collection.count(queryParams);

Feedback and Knowledge Base