union on same collection in mongodb

mongodb, mongodb-query

Solution

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.

Problem

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.

Original source