Query based on fields from 2 different Collections in MongoDB

Question

I am developing web application using Gin and MongoDB. I have defined models for user like,

type User struct {
    Id           nftdb.ObjID `bson:"_id,omitempty"`
    Tenant       string
    Name         string //common
    Email        string //common
    Phone        string //common
    PAN          string
    ProfilePic   string
    Modules      []string
    Role         string //CA,Individual,Business,Admin,etc.
    AuthType     string
    AuthData     map[string]any
    Address      Address
    Registered   bool           //Registration Tracking
    Verified     bool           // CA onBoarding Tracking
    Status       string         // Active,Inactive
    Extras       map[string]any //keep all extra variable info in this!
    ModifiedBy   string
    ModifiedDate time.Time
}

And Application model like this,

type Application struct {
    Id            nftdb.ObjID `bson:"_id,omitempty"`
    Tenant        string
    Type          string //CA, USER
    User          string //Refers to Userid
    UserType      string
    SubmittedDate time.Time //Application submitted date
    FinancialYear string    //ITR Application
    ModuleInfo    []string  //ITR Plans, Loan Selection
    PaymentInfo   *PaymentSummary
    Reviewer      string //Admin,CA (if ITR)
    Comments      string //Application Comments
    Status        string 
    ModifiedBy    string
    ModifiedDate  time.Time
}

Every user has an application linked to him.I have to provide an endpoint which shows data like
"Username,EmailId,PhoneNumber,ApplicationSubmittedDate,Modules,ApplicationStatus". And also
I can filter the data based on Application Submitted Date and search by username.
Now How do I structure the query such that,I can get the intended data. Do I have to store the username field in Application so that querying based on username will be easier.But this will lead to data duplication.How do we approach these cases where we have to fetch data from 2 or more different collections and also need to provide filters based on different fields in different collections?

I have tried querying the applications collection first and after that filtered the Users collection from the ids which we get earlier from Application response. And but I am unable to get desired response when applying both filters like filtering applications within date range and search by username .For search by username,I am using regex search on the username field with case insensitive options.
From some blogs ,I have found out that ,we can used lookup (aggregation) to fetch results.But can we do it without aggregation ?

WeDoTheBest4You · Accepted Answer · 2025-08-01 05:30:08Z

0

Since you are in MongoDB which is an aggregate oriented database or document database, you may be able to have nested data structure. It means Application data can be stored inside user data. The Application data can be embedded in the documents of user data.

Now this new structure will take away the need to do a lookup since there is only one collection to query. By querying user data collection, you would get the desired data.

Please follow this link to find more about data embedding in MongoDB.

Embedding MongoDB

answered Aug 1 at 5:30

WeDoTheBest4You

2,1601 gold badge3 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Akon_Namikaze Aug 2 at 15:06

Thanks for answering. Basically my requirement is like this, User will register into website with basic user details,then after signup, he will start drafting an application (specific to user type). There are steps involved in submitting application like selecting modules, uploading files, completing payment , application is completed once payment is done. If I store application data in user collection as embedded document, there are multiple db update calls, which will happen in user collection and application collection based on certain actions.Does this have any performance issues ?

WeDoTheBest4You Aug 4 at 3:53

The point of concern is the cost of join operation for any kind of normalised data. We need to weigh this cost against the possible options. What if the frequently referenced fields - userName, emailId, phoneNumber, are also stored in the collection Application. Would the cost of keeping it in synch outweigh the cost of join ? What if the collection Application as such embedded in user ? Would the cost of updating the embedded documents outweigh the cost of join ? if the two questions answer the cost of join will not be outweighed by the two methods, we may need to go with join.

WeDoTheBest4You Aug 4 at 3:56

These references may find useful: Performance difference between storing the asset as subdocument vs single document in Mongoose stackoverflow.com/questions/58096033/… mongodb/mongoose - when to use subdocument and when to use new collection stackoverflow.com/questions/44388414/…

Collectives™ on Stack Overflow

Query based on fields from 2 different Collections in MongoDB

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related