Sort by value, where value is in another collection and ID is stored in collection where we apply sort

Question

I have next situation.

In parameters collection I have documents which have key groups where values are ids of documents from another collection. That is something like a FOREIGN key which references to another collection.

In another collection we have documents that corresponds to the '_ids' which are stored in main parameters collection.

Here is one sample document from parameters collection:

{
    "_id" : ObjectId("538726134ba2222c0c0248b6"),
    "name" : "Potta",
    "groups" : [ 
        "54c91b2c4ba222182e636943"
    ]
}

I need to sort by groups, but as you can see in main collection groups values are ID's, but I want to sort by group names.

Here is one sample collection from groups collection.

{
    "_id" : ObjectId("54c91b2c4ba222182e636943"),
    "name" : "Group 01",
}

Is this possible to achieve in Mongo DB?

Thanks

Answer 1

Given the datas are

> db.parameters.find({})
{ "_id" : ObjectId("56cac0cd0b5a1ffab1bd6c12"), "name" : "potta", "groups" : [ "
123", "234" ] }
> db.groups.find({})
{ "_id" : "123", "name" : "Group01" }
{ "_id" : "234", "name" : "Group02" }

Under mongodb 3.2, you can do it through $lookup to perform a left outer join to an unsharded collection in the same database, then to sort the group name as below.

> db.parameters.aggregate([
                          {$unwind: '$groups'}, 
                          {$lookup: {
                                    from: 'groups', 
                                    localField: 'groups', 
                                    foreignField: '_id', 
                                    as: 'gr'}},
                           {$sort: {'gr.name': 1}}])

---

For under 3.2, please try to do it as following

> var pa = db.parameters.find({});
> pa.forEach(function(doc) {
                var ret = db.groups
                            .find({_id: {$in: doc.groups}})
                            .sort({name: 1}); 
                ret.forEach(printjson)
});

---

Or you could do it through mapReduce as below

// emit group id from parameters collection
> var map_param = function() { 
            var that = this; 
            this.groups.forEach(function(g){emit(that._id, g);})};

// emit group id and name from group collection
> var map_group = function() {emit(this._id, this.name);}

// combine those results from map functions above
> var r = function(k, v) {
            var result = {id: '', name: ''};
            v.forEach(function(val){
                   if (val.id !== null){ result.id = val;} 
                   if (val.name !== null) {result.name = val;}
            }); 
            return result;};

> db.parameters.mapReduce(map_param, r, {out: {reduce: 'joined'}})

> db.groups.mapReduce(map_group, r, {out: {reduce: 'joined'}, sort: {name: 1}})

Eventually, the sorted results are in the joined collection.