Concat two collections and skip+limit

Question

I have two collections, collectionA and collectionB and they are NOT related (i.e no 'foreign key' stuff). They represent two similar datasets, for arguments sake, let's say that collectionA is a collection of bird species, and collectionB of fish species

I need to query these both, but with skip and limit operators, so I need to concatenate the queries into one and apply skip and limit before the query is executed, because the collections have crazy volumes of data that will be frequently accessed, so the skip/limit is necessary, but I don't want the skip/limit to simply first reach over collectionA, and then reach into collectionB, I want them to access documents in the same increasing order as the timestamp in the _id's, so an example result might be

start = 3
limit = 2

bird.1 # skip
bird.2 # skip
fish.1 # skip
fish.2 # return this
fish.3 # return this
fish.4 # EOQ
bird.3

I'm fairly new to MongoDB so I apologize if this is unclear.

Here is an example JSON as per request. The collections are very similar, with only 1 field differing (I did not design this)

# collectionA
{
  _id: ObjectId('507f1f77bcf86cd799439011'),
  species: 'Falcon',
  beak_type: 'hooked',
  hates_fish: True
}

# collectionB
{
  _id: ObjectId('507f1f77bcf86cd799439012'),
  species: 'Haddock'
  hates_fish: True
}

can you please add sample collection document for A and B preferable in a JSON format? — Mallik, Oct 20 '20 at 09:40

score 0 · Accepted Answer · answered Oct 20 '20 at 14:23

MongoDB operatins are designed to work on single collections. If you come from a relational database background, this might seem odd, because in the SQL world you are used to doing joins to link tables together; but MongoDB is not SQL!

So looking at your data, the question really is: why do you have the data in two collections? The data looks pretty much the same structure. I would recommend having a single collection, then all the operations become simpler.

If you are stuck with having two collections, then there are already numerous answers on this site on how to use the $lookup stage using an aggregate query, e.g. How to join multiple collections with $lookup in mongodb and others.

Mallik · Answer 2 · 2020-10-20T19:54:22.703

//you can now use a new feature in Mongo 4.4.1 to combine documents in two collections
//Working code
//query data in collection as given in problem statement
> db.colA.find();
{ "_id" : ObjectId("507f1f77bcf86cd799439011"), "species" : "Falcon", "beak_type" : "hooked", "hates_fish" : "True" }
> db.colB.find();
{ "_id" : ObjectId("507f1f77bcf86cd799439012"), "species" : "Haddock", "hates_fish" : "True" }
//use aggregate and $unionWith to acheive your goal
> db.colA.aggregate([
... {$project:{species:1,hates_fish:1,_id:0}},
... {$unionWith:{coll:"colB",pipeline:[{$project:{species:1,hates_fish:1,_id:0}}]}}
... ]);
{ "species" : "Falcon", "hates_fish" : "True" }
{ "species" : "Haddock", "hates_fish" : "True" }
> print("MongoDB",db.version());
MongoDB 4.4.1
>
//if you currently don't have 4.4.1 and you want to use this feature for your task, 
//please upgrade to Mongo DB 4.4.1, sooner or later you need to upgrade!
//you can further add sort and skip commands in the same aggregate query
//example below:
> db.colA.aggregate([
... {$project:{species:1,hates_fish:1,_id:0}},
... {$unionWith:{coll:"colB",pipeline:[{$project:{species:1,hates_fish:1,_id:0}}]}},
... {$sort:{species:-1}}
... ]);
{ "species" : "Haddock", "hates_fish" : "True" }
{ "species" : "Falcon", "hates_fish" : "True" }
> db.colA.aggregate([
... {$project:{species:1,hates_fish:1,_id:0}},
... {$unionWith:{coll:"colB",pipeline:[{$project:{species:1,hates_fish:1,_id:0}}]}},
... {$sort:{species:-1}},
... {$skip:1}
... ]);
{ "species" : "Falcon", "hates_fish" : "True" }
>

Concat two collections and skip+limit

2 Answers2