union on same collection in mongodb

Question

I need the most viable way to search docs with the following structure.

{ _id:"",
var1: number,
var2: number,
var3: number,
}

In the sql way the resultset i need would be UNION of these three (n records sorted by var1,n records sorted by var2,n records sorted by var3)

with UNION I expect the duplicates to be removed.

Being new to mongodb i am not able to find the right way to write a query for such an operation, i believe it must be possible in mongodb.

If in case its not possible, could you please suggest an alternate nosql solution.

Answer 1

The closest MongoDB operator to what you are looking for is an $or, but that isn't quite the same as an SQL UNION which combines two separate queries into a single result. MongoDB queries are always against a single collection, but $or allows you to have multiple query clauses.

For example:

db.collection.find(
    // Find documents matching any of these values
    {$or:[
        {var1: 123},
        {var2: 456},
        {var3: 789}
    ]}
).sort(
    // Sort in ascending order
    {var1:1, var2:1, var3:1}
)

Since you are limited to querying a single collection, results will already be de-duplicated at the document level and all results will share the same sort order if one is specified.

If you want to simulate a UNION (or other operation working with multiple collections/queries) in MongoDB, you will have to write multiple queries and merge the result sets in your application code.