MongoDb sort Collection by count in other Collection

I have two collections: collection A and collection B. Collection B "A_id" will have the _id from Collection A (kinda like a relationship).

Collection A

{
  _id,
  name
}

Collection B

{
 _id,
 A_id,
 other_id
}

I need to sort the documents from Collection A by the amount of repetitions of documents to an A_id in Collection B (sort by count if you may). The constraint is that we don't want to have an array in collection A because the documents in B will grow a lot and will be updated constantly.

Any idea how to perform this kind of sorting?

Thanks much

There are many ways to do what you're asking. You have to know some things about the data first. How many documents will a B document have?

  • It looks like a case where you'd want Collection A to have an items field with documents that are B. There is a 16 MB limit for that, but for most cases works just fine.

  • Keep a count on A with numbers of B records inserted. (Ie every time you insert a B you'd $inc the field on A). You'd then do an index on the count field for A and do sort({count: -1})

It is my understanding that this is not strictly possible. However, you could use the aggregation framework group function and $sum accumulator to group by A_id which will give you a mapping between A_id and the total number of documents in collection B with that A_id. Sort that mapping and then you can select things from collection A in the order you have described, although you'd have to select from collection A one at a time since you are enforcing an ordering at selection time.

The other option would be to have some count field in collection A on each document that you include logic to increment every time something is inserted into in collection B with a particular A_id.