CODEWITHSUNDEEP
node.jsmongodbsortingmongoose
Alexey Churak
Alexey Churak

Reputation: 33

Mongodb sort by either of two fields

Is there a way to sort by two fields at the same time in mongodb? So let's say we have the following documents in the collection:

{ id: 'name1', last_1: 5, last_2: 2 },
{ id: 'name2', last_1: 1, last_2: 9 },
{ id: 'name3', last_1: 4, last_2: 3 }

I want it to be sorted in descending order, checking both last_1 and last_2, so the result would be: document2 (9 in last_2), document1 (5 in last_1), document3 (4 in last_1). I also need to know which field it is in the result. My current aggregation looks like this:

{ $group:
      { _id: { id: '$name' },
        last_1: { $last: '$field1' },
        last_2: { $last: '$field2' },
      }
      },
      { $sort : { /* don't know what to put in here */ } },
      { $limit: some_limit }

UPD: I'll probably have to remove sort/limit at all and use some custom node function for that, because it looks like I can't achieve it using just mongo, especially because the additional requirement would be to list the same document twice if both its fields are bigger than in the other documents.

Upvotes: 0

Views: 2951

Answers (3)

Alexey Churak
Alexey Churak

Reputation: 33

Ok, this is how it ended up, I was afraid it'd be too slow in compare with something mongodb-native, but it's actually not that bad:

Test.aggregate(
        [
          { $match:
          {'modified': { $gte: settings.period } }
          },

          { $group:
          { _id: { name: '$name' },
            last_1: { $last: '$field_1' },
            last_2: { $last: '$field_2' },
          }
          }
        ],

        function (err, result) {
          if (err) callback(err);
          else {
            var arr = [];
            for (var i = 0; i < result.length; i++) {
              var obj = result[i];
              arr.push({ id: obj._id, sort: obj.last_1 });
              arr.push({ id: obj._id, sort: obj.last_2 });
            }
            arr.sort(compare).reverse();
            arr = arr.slice(0, settings.limit);
            var arr2 = [];
            for (i = 0; i < arr.length; i++) {
              var id = arr[i].id;
              var element = result.filter(function(element) {
                return element._id == id;
              });
              arr2.push(element[0]);
            }
            callback(null, arr2);
          }
        });
      );

function compare(a, b) {
  if (a.sort < b.sort)
    return -1;
  if (a.sort > b.sort)
    return 1;
  return 0;
}

Upvotes: 0

Oleksandr T.
Oleksandr T.

Reputation: 77482

Maybe something like this,

1

db.test.aggregate([
    {$project: {name: 1, field1: 1, field2: 1}},
    {$group: {_id: '$name', field_1: { $last: '$field1' }, field_2: { $last: '$field2' }}},
    {$project: {name: 1, field_1: 1, field_2: 1, cmp: { $cmp: ['$field_2', '$field_1'] } }},
    {$sort: { cmp: -1, field_1: -1, field_2: -1 }}
])

2

db.test.aggregate([
    {$project: {name: 1, field1: 1, field2: 1}},
    {$group: {_id: '$name', field_1: { $last: '$field1' }, field_2: { $last: '$field2' }}},
    {$project: {
        name: 1, 
        field_1: 1, 
        field_2: 1, 
        largest: { $cond: [ {$gt: ['$field_2', '$field_1']}, '$field_2', '$field_1'] }    
    }},
    {$sort: { largest: -1 }}
])

Upvotes: 4

BatScream
BatScream

Reputation: 19700

Your requirement being to sort your documents in the collection based on the fields, last_1 and last_2, whichever is largest among them can be aggregated as below:

  • Project an extra field which holds the greater value among last_1 and last_2.
  • Based on this field sort the records in descending order.
  • Then project the required fields.

The aggregation code:

db.collection.aggregate([
{$project:{"id":"$id",
           "last_1":1,
           "last_2":1,
           "sortField":{$cond:[
                               {$gt:["$last_1","$last_2"]},
                               "$last_1",
                               "$last_2"]}}},
{$sort:{"sortField":-1}},
{$project:{"_id":0,
           "id":"$id",
           "last_1":1,
           "last_2":1}}
])

Upvotes: 1

Related Questions