MongoDB: Should I use an aggregation pipeline or maintain a field count for reference counts?

[Sunny-unik]

I'm working on a MongoDB application with two collections, A and B. In collection A, there are multiple documents that contain a field referencing the ID of a document in collection B.

I want to calculate and store a count of how many documents in collection A reference each document in collection B. I have considered two approaches:

1. Aggregation Pipeline with $lookup and $addFields:

• Use an aggregation pipeline on collection B with a $lookup stage to join collection A, and an $addFields stage to add a new field representing the count of documents in A that reference each document in B.
• Pros: Always provides data consistency, and avoids data duplication.
• Cons: Can be computationally expensive, especially with large datasets, and queries can become complex.

2. Maintain a countOfA field in collection B:

• Maintain a countOfA field in each document in collection B that stores the count of documents in A that reference it. Increment/decrement this field during add/update/delete operations in collection A.
• Pros: Query performance is faster because the count is pre-calculated, and the queries are straightforward.
• Cons: There is potential for data inconsistency if updates fail, and managing concurrency could be complex.

I'm having trouble deciding which approach to use. Which one would be the best practice and more suitable for production use? Are there any potential pitfalls or additional considerations I should be aware of for each approach?