CODEWITHSUNDEEP
mongodb
Michel Beaussart
Michel Beaussart

Reputation: 111

Mongodb: What is the best way to search multiple collections for the same field in one query

I inherited a bunch of collections and each of them have fields in common. Is there a way to query the "EMAIL" field in collection A and Collection B ....or Am I stuck with iterating through the collections programmatically ?

db.colA.find({"EMAIL":"[email protected]"})

Collection A : { "_id" : ObjectId("58197fc91b69ba68721d4148"), "UUID" : "0b6827f2-9384-11e0-8f4a-b8ac6f949be6", "EMAIL" : "[email protected]", "FNAME" : "JOHN", "LNAME" : "DOE"}

Collection B: { "_id" : ObjectId("4ed234423654fea654a654f2"), "SOURCE" : "65488451522", "EMAIL" : "[email protected]", "FN" : "JOHN", "DOB":"05/13/1967"}

Expected behavior: the search query would returned both records.

Well, why not query Col A and Col B .. I have 17 collections in the DB, one query would be great.

Upvotes: 2

Views: 1419

Answers (4)

Tan Kim Loong
Tan Kim Loong

Reputation: 1035

There's no way to do that. You're stuck to having to do that programmatically. Although MongoDB has embedded documents and an 'entity' might be scatted in many collections, there should be one collection which represents the complete entity

For example, you might have a UserProfile collection but snippets of user profile might be embedded in other collections. In that case when you need the complete user's profile, you query it straight from UserProfile collection.

Upvotes: 0

felix
felix

Reputation: 9285

You can't do this in a single query with your current collections. You may be able to achieve this with DBRefs, but it will soon be deprecated (if not already), so don't use it unless it's absolutely necessary.

Even if running the same query on 17 distincts collections is not very elegant, I guess this is still the best option

Upvotes: 1

mr.freeze
mr.freeze

Reputation: 14062

You can use the aggregation lookup to achieve this:

db.colA.aggregate([
    {
      $lookup:
        {
          from: "colB",
          localField: "EMAIL",
          foreignField: "EMAIL",
          as: "connected_query"
        }
   }
])

You will then have a field called connected_query in your results that refer to colB results that have the same email as colA.

Upvotes: 1

HARDI
HARDI

Reputation: 394

If you are talking about joins, $lookup has been enabled in mongo latest version. Java doc: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

Upvotes: 0

Related Questions