MongoDB: querying against multiple concatenated collections

i have hot multiple collections which has got data of the same type and schema.

all i want is mongodb to look at them as they are single collection

i don't want to join collections.i just want to query them as if they are a single collection with consecutive documents.all documents has got the same schema.i know it's a special usecase.others keep talking about aggregating collection based on a column and doing a sql join.

i want the query to sort the collection based on the insert time

suppose that we have got a collection named C1 which has got C1_d1,c1_d2,c1_d3 documents and the collection C2 has got the C2_d1 and C2_d2 and suppose that first C2_d1 has been inserted to C2 and then C1_d1 to C1, then C2_d2 has been inserted to C2 and then the rest of C1 documents

now i want to query C1 and C2 simulteniously, so it should consider documents in this order:

C2_d1 , C1_d1 , C2_d2 , C1_d2 , C1_d3 as if they are in a single collection

i appreciate if you describe it with the jvm driver

You can achieve this by just querying using an aggregation pipeline of the following:

db.C1.aggregate([{ $unionWith: "C2" }])
{ "_id" : ObjectId("5f5895b5c5140b67d2d6cbcf"), "name" : "C1_d1" }
{ "_id" : ObjectId("5f5895b5c5140b67d2d6cbd0"), "name" : "C1_d2" }
{ "_id" : ObjectId("5f5895b5c5140b67d2d6cbd1"), "name" : "C1_d3" }
{ "_id" : ObjectId("5f5895bec5140b67d2d6cbd2"), "name" : "C2_d1" }
{ "_id" : ObjectId("5f5895bec5140b67d2d6cbd3"), "name" : "C2_d2" }
{ "_id" : ObjectId("5f5895bec5140b67d2d6cbd4"), "name" : "C1_d3" }

This will union both C1 and C2 collections, if you need to have these sorted in some way, you can add a sort stage to the end.

db.C1.aggregate([
  { $unionWith: "C2" },
  { $sort: {field1: 1} }
])

If you're always querying like this you might want to put this in a view to make your life a bit easier, to create a view of a pipeline use the following code:

db.createView("View1",
  "C1",
  [{ $unionWith: "C2" }]
);

Now if we insert some data in to our MongoDB collections.

db.C1.insertMany([
 {name: "C1_d1"},
 {name: "C1_d2"},
 {name: "C1_d3"}
]);

db.C2.insertMany([
 {name: "C2_d1"},
 {name: "C2_d2"},
 {name: "C1_d3"}
]);

We'll be able to query the view and get back our results.

db.View1.find()
{ "_id" : ObjectId("5f5895b5c5140b67d2d6cbcf"), "name" : "C1_d1" }
{ "_id" : ObjectId("5f5895b5c5140b67d2d6cbd0"), "name" : "C1_d2" }
{ "_id" : ObjectId("5f5895b5c5140b67d2d6cbd1"), "name" : "C1_d3" }
{ "_id" : ObjectId("5f5895bec5140b67d2d6cbd2"), "name" : "C2_d1" }
{ "_id" : ObjectId("5f5895bec5140b67d2d6cbd3"), "name" : "C2_d2" }
{ "_id" : ObjectId("5f5895bec5140b67d2d6cbd4"), "name" : "C1_d3" }

Also, within your java code you'll be able to access the view with

MongoClient mongoClient = new MongoClient();
DB database = mongoClient.getDB("test");
DBCollection collection = database.getCollection("View1");

If you get the following error when creating the view:

Invalid pipeline for view test.View1 :: caused by :: $unionWith is not allowed in the current feature compatibility version. See https://docs.mongodb.com/master/release-notes/4.4-compatibility/#feature-compatibility for more information.

you can turn on the feature with the following command

db.adminCommand( { setFeatureCompatibilityVersion: "4.4" } )

in addition to the answer above which is enough, i want to share the jvm api version of it.there are some aggregate stages which can be used, in the aggregate function whether you are using Java api or not.

for example you can skip some result with $skip stage or limit the result with $limit stage.the $unionWith is the newest stage which only works on v4.4 and i did not know that.as i'm speaking now the current version is 4.4 and i updated my MongoDB server and i was able to use that stage.others may use aggregates for more complex situations like doing joins and grouping and etc...

so with that said, we can combine two(or more) collection into one and do a query on it:

    val observable = MongoDBGateway.mongoClient.getDatabase("DB1")
            .getCollection("C1")
            .aggregate(mutableListOf(

                    Document("$unionWith", "C2"),

                    Document("$sort", Document("Data",1)),

                    Document("$match", Filters.or(
                            Filters.eq("Data",2),
                            Filters.eq("Data",1)
                    )),

                    Document("$skip", 2),
                    Document("$limit", 2),
            ))

    val iterator = observable.iterator()

    while (iterator.hasNext())
        println(iterator.next())

i have used Kotlin language, but it's same on any jvm language.as you can see, i have concatenated the C1 collection with C2 with the help of $unionWith stage.then immediately i have sorted the result on Data column ascending and then i have done a query on the result of last stage where Data column is 1 or 2 and then i have skipped 2 of the results and the i have limitted the result

so although we don't have access to the FindIterable result to do queries and limits or skip, we could do all that with the stages.they may provide an api later that does not need to know about stages.

one last thing, pay attention to the order of stages.because the result of each stage is piped to the next.for example if we put $limit stage first, it will first get the first 2 documents of C1 and then it will do the rest and the total result will be absolutely different.so first do the union and then sort and then match and then skip and then limit.