mongodb - perform batch query

Question

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?

possible duplicate of [How do I perform the SQL Join equivalent in MongoDB?](http://stackoverflow.com/questions/2350495/how-do-i-perform-the-sql-join-equivalent-in-mongodb) — user1937198, Feb 09 '14 at 17:11

score 21 · Accepted Answer · answered Feb 09 '14 at 17:11

21

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] }})

answered Feb 09 '14 at 17:11

WiredPrairie

58,954
17
116
143

is it not inefficient when mongodb is hosted on remote network and number of document id's are of considerable high amout as it it will take double bandwidth first to load and than to upload – Ravinder Payal Sep 25 '15 at 03:11
1

@RavinderPayal-inefficient compared to what? Sometimes MongoDB solutions require creative document design to avoid unnecessary work. And, more importantly, it's not always the best choice for a database. – WiredPrairie Sep 25 '15 at 10:34
i asking this because i am currently developing social network platform and let a user have 1000 friends saved in mongodb now to find posts by those 1000 friends i need to load id's of those 1000 peoples from remote db and now need to reupload that data to that remote db which contains posts – Ravinder Payal Sep 25 '15 at 13:49
Whichever works for you best: one call with a high payload, or 1000 calls with a low payload. – noderman Feb 19 '16 at 20:44
Yes its ineffecient to do this as two queries, this is an old answer perhaps it was the only way at the time but today you would use $lookup. – justin.m.chase Aug 03 '21 at 02:23

score 1 · Answer 2 · answered Apr 14 '19 at 18:22

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.

score 1 · Answer 3 · answered Aug 03 '21 at 02:22

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" }
  ]
}

mongodb - perform batch query

3 Answers3

Linked