CODEWITHSUNDEEP
mongodb
jean
jean

Reputation: 2980

mongodb - perform batch query

I need query data from collection a first, then according to those data, query from collection b. Such as:

For each id queried from a
    query data from b where "_id" == id

In SQL, this can be done by join table a & b in a single select. But in mongodb, it needs do multi query, it seems inefficient, doesn't it? Or it can be done by just 2 queries?(one for a, another for b, rather than 1 plus n) I know NoSQL doesn't support join, but is there a way to batch execute queries in for loop into a single query?

Upvotes: 14

Views: 21408

Answers (3)

justin.m.chase
justin.m.chase

Reputation: 13655

This is basically a join in SQL parlance and you can do it in mongo using an aggregate query called $lookup.

For example if you had some types in collections like these:

interface IFoo {
  _id: ObjectId
  name: string
}
interface IBar {
  _id: ObjectId
  fooId: ObjectId
  title: string
}

Then query with an aggregate like this:

await db.foos.aggregate([
  {
    $match: { _id: { $in: ids } } // query criteria here
  },
  {
    $lookup: {
      from: 'bars',
      localField: '_id',
      foreignField: 'fooId',
      as: 'bars'
    }
  }
])

May produce resulting objects like this:

{
  "_id": "foo0",
  "name": "example foo",
  "bars": [
    { _id: "bar0", "fooId": "foo0", title: "crow bar" }
  ]
}

Upvotes: 1

Naved Ahmad
Naved Ahmad

Reputation: 821

It's very simple, don't make so many DB calls for each id, it is very inefficient, it is possible to execute a single query which will return all documents relevant to each of the ids in a single pass using the $in operator in MongoDB, which is synonymous to in syntax in SQL so for example if you need to find out the documents for 5 ids in a single pass then

const ids = ['id1', 'id2', 'id3', 'id4', 'id5'];
const results = db.collectionName.find({ _id : { $in : ids }})

This will get you all the relevant documents in a single pass.

Upvotes: 1

WiredPrairie
WiredPrairie

Reputation: 59763

You'll need to do it as two steps.

Look into the $in operator (reference) which allows passing an array of _ids for example. Many would suggest you do those in batches of, say, 1000 _ids. 

db.myCollection.find({ _id : { $in : [ 1, 2, 3, 4] }})

Upvotes: 21

Related Questions