CODEWITHSUNDEEP
azure-cosmosdb
Luis Delgado
Luis Delgado

Reputation: 3734

DocumentDB - how to order results based on unique ID count

We have an application that allows users to "follow" other users. When a user follows another, we register this data as a document within documentDB, like this:

{
    "followerId": "userUUID",
    "artistId": "artistUserUUID"
}

We now want to get a list of artists, ordered by the count of followers they have. So I am looking to somehow ask the DB to, based on these documents, give me back an array of artistUserUUId's, ordered by the amount of followers they have registered (as expressed in documents like the example given above).

Alternatively, we are also open to add an Array property to the document of the artistUser themselves, though even in this scenario I am still unsure how to do an ORDER BY based on the counting of a document's property (this property being an array of follower Ids).

I guess a workaround would be to add a stored procedure or trigger that will update a counter property within the artistUser document, but I'd like to validate if these is a way to implement this counting feature natively without such a trick.

Upvotes: 0

Views: 125

Answers (1)

Larry Maccherone
Larry Maccherone

Reputation: 9523

Unless you denormalize the follower count into artist user documents (as you suggest), then you'll have to fetch every follower to accomplish your goal. Fetching every follower document, may or may not be prohibitive depending upon how many there are. If you fetch them only into a stored procedure rather than your actual client, it's conceptually no less efficient than an SQL GROUP_BY clause. Design your stored procedure to do the count and only returns the table of artist and counts. A robust implementation would incrementally update your output table in pages and be able to restart where it left off after a stored procedure timeout. Look at my countDocuments example stored procedure in documentdb-mock as well as my "Pattern for writing stored procedures" in the documentation for documentdb-utils for how I typically accomplish this.

Upvotes: 1

Related Questions