select 2 fields and return a sorted array with their distinct values

Question

Say we have the following docs:

{a: 1, b: 2},
{a: 2, b: 0},
{a: 3, b: 1}

I want a query that will return:

[0, 1, 2, 3]

I want to know if there is a way to do this faster than:

• just making 2 queries, one selecting a, the other selecting b then merging in my application.
• using map reduce(which was reaaaaly slow compared to the previous method)

Answer 1

You need to $group our documents and use the $push accumulator operator to return an array of "a" and "b" within the collection.

In the $project operator you use the $setUnion operator to filter out the duplicates.

db.coll.aggregate(
    [
        { "$group": { 
            "_id": null, 
            "a": { "$push": "$a" }, 
            "b": { "$push": "$b" } 
        }}, 
        { "$project": {
            "_id": 0, 
            "merged": { "$setUnion": [ "$a", "$b" ] } 
        }} 
    ]
)

which produces:

{ "merged" : [ 3, 2, 0, 1 ] }

Answer 2

You could use something like that

db.getCollection('yourdocs').aggregate([
     { 
        $project: { 
            values: [ "$a", "$b" ] 
        } 
     },
     {
         $unwind: "$values"
     },
     {
         $group: {
             _id: "distinctValues",
             values: {
                 $addToSet: "$values"
             }
         }
     }
])

which will result in

{
    "_id" : "distinctValues",
    "values" : [ 
        0, 
        1, 
        2, 
        3
    ]
}

Update: Just read you want it sorted which is not guaranteed with $addToSet. If sorting is crucial for you just add the following to the aggregation pipeline after the stages above

{
    "$unwind": "$values"
},
{
    "$sort": { 
        "values": 1
    }
},
{
    "$group": { 
        _id: "distinctValues",
        "values": {
            "$push": "$values"
        }
    }
}