How to fetch a data from two collections at a same time in MongoDB?

Question

eg. I have two collections (i.e C1, C2) both collections have same columns (i.e _id, Message, Date) and I want to fetch only 50 documents from C1 and C2 in ascending order of date.

I am getting data from one collection only by using following query on mongo shell

db.C1.find({"date" : { "$gt" : ISODate("2018-05-18T00:00:00.203+0000")}})
 .sort({ "date" : 1.0 }).limit(50);

but I want data from both collections.

to fetch data from other collection you need to make another find query. Or do you want to perform join in on two collections? — Atish, May 19 '18 at 07:33
No, I can't perform join because for join i need to match columns which i don't have. — Namrata Kamble, May 19 '18 at 08:40
but I want data from both collections > Do you want to do it in a single query? or something else — Atish, May 19 '18 at 08:46
can you post how your output should look like? and one sample document from both collections? — Atish, May 19 '18 at 08:51

score 0 · Answer 1 · answered May 19 '18 at 07:38

0

You need to run two different queries to fetch data from two collections using find query:

db.C1.find({"date" : { "$gt" : ISODate("2018-05-18T00:00:00.203+0000")}}) .sort({ "date" : 1.0 }).limit(50);

db.C2.find({"date" : { "$gt" : ISODate("2018-05-18T00:00:00.203+0000")}}) .sort({ "date" : 1.0 }).limit(50);

You can alternatively consider using MongoDB's $lookup to join data from two collections have a foreign field in another collection:

db.C1.aggregate([
   {
     $lookup:
       {
         from: "C2",
         localField: "date",
         foreignField: "date",
         as: "C2_data"
       }
  }
])

answered May 19 '18 at 07:38

Atish

4,277
2
24
32

one field should have be the same value to apply `$lookup` – Ashh May 19 '18 at 08:17
$lookup will provide me left join concept which I don't want. I want 50 documents in ascending order from 2 collections. – Namrata Kamble May 19 '18 at 08:50
eg. C1 is having { "_id" : ObjectId("5afe48b7d945612900a955cf"), "message" : "ABCD", "date" : "2018-05-18T03:29:59.460+0000" }, { "_id" : ObjectId("5afe48b7d945612900a955d0"), "message" : "EFGH", "date" : "2018-05-18T03:29:59.461+0000" } C2 is having{ "_id" : ObjectId("5afe48b7d945612900a955d1"), "message" : "IJKL", "date" : "2018-05-18T03:29:59.462+0000" } { "_id" : ObjectId("5afe48b7d945612900a955d2"), "message" : "MNOP", "date" : "2018-05-18T03:29:59.463+0000" } *My O/P wl display all 4* – Namrata Kamble May 19 '18 at 08:59
in that case you will have two make two queries to DB – Atish May 19 '18 at 09:05
if I'll get output from two queries then manually or through code I have to marge both output and make it in ascending order and then display only latest 50 documents. which is not possible with bulk data. – Namrata Kamble May 19 '18 at 09:13
you can consider storing this data in single collection and add key to differentiate between them. For example product_id: 1, product_id:2. Using this you can fetch required documents in a single query avoiding additional efforts in code and manual sorting – Atish May 19 '18 at 09:16

How to fetch a data from two collections at a same time in MongoDB?

1 Answers1