Hello @Jose_Kj, welcome to the community.
i though of using aggregate lookup queries, but it seems it will have some issues while sharding. I am currently in db design phase only.
The docs mention one restriction for the lookup operation: Sharded Collection Restrictions for $lookup. But, there is also a workaround mentioned for that issue.
The most important aspect of sharding is the selection of the Shard Key. This is a design aspect. There are a few rules guiding the shard key selection, and please be aware of them. Shard key is important - as the number of shards, the data distribution and the performance of queries depend upon it.
It is a good idea to think about the shard key during the design phase of the database as also the application.
Also is it anti-pattern to store member_ids(maybe few 100s) as array in group table.i read like updating arrays will include lot of resource usage.
There are no restrictions in storing an array of data (e.g., member id’s in a group collection), as long as the number of members stored in the array is definite (means that you know ahead that there will be at most 100 or 1000 of them), and not growing indefinitely.
As far as working with arrays, MongoDB Query Language (MQL) has various operators to query and update array data. In addition, there are Aggregation Array and Set Expression Operators. These are optimized to be used with arrays.
To get best performance, the array data can be indexed, and these indexes are called Multikey Indexes. There are various tools and techniques which can be used to optimize your queries, to use the indexes.