CODEWITHSUNDEEP
javascriptmongodb
Zeeshan Hassan Memon
Zeeshan Hassan Memon

Reputation: 8325

Mongoose best query - sort on two attributes

Sample mongodb records:

[
    {first: "Some", middle: "A", last: ""},
    {first: "Some", middle: "B", last: ""},
    {first: "Some", middle: "", last: "C"},
    {first: "Some", middle: "D", last: ""}
]

Outputs:

If .sort({middle:1,last:1})  =====> C,A,B,D
If .sort({last:1, middle:1}) =====> A,B,D,C
If [what here ? ]            =====> A,B,C,D

Upvotes: 1

Views: 71

Answers (3)

Zeeshan Hassan Memon
Zeeshan Hassan Memon

Reputation: 8325

I have found this solution.

db.getCollection('dummy').aggregate(
   [
      { $project: { merge: { $concat: [ "$middle", "$last" ] },middle: '$middle',last:'$last' } }
   ]
).result.sort(function(a, b) {
  return a.merge > b.merge ? 1 : -1;
})

Upvotes: 1

bagrat
bagrat

Reputation: 7428

You can use the aggregation framework to transform your data into a form, where you can sort it in the way you desire. Try this aggregation query:

db.yourCollection.aggregate(
    [
        {$project: 
            {
                first: 1, 
                rest: {$cond: [{$eq: ['$middle', '']}, 
                '$last', '$middle']}, 
                isMiddle: {$cond: [{$eq: ['$middle', '']}, false, true]}
            }
        }, 
        {$sort: {rest: 1}}, 
        {$project: 
            {
                first: 1, 
                middle: {$cond: ['$isMiddle', '$rest', '']}, 
                last: {$cond: ['$isMiddle', '', '$rest']}
            }
        }
    ]
)

This will get you what you want:

{ "first" : "Some", "middle" : "A", "last" : "" }
{ "first" : "Some", "middle" : "B", "last" : "" }
{ "first" : "Some", "middle" : "", "last" : "C" }
{ "first" : "Some", "middle" : "D", "last" : "" }

The problem with regular sorting is that the middle and last are separate keys and are sorted independently (although stably). SO the only way to achieve your goal is to combine them in a single key.

Upvotes: 1

Tim B
Tim B

Reputation: 41188

Your problem is that you are trying to sort on two fields, using one if the other is not blank. No simple sort will provide that as it always sorts by one field and then the other.

You will need to do this as a multi-step query. First take the data and generate a new field to sort on that contains last, if last is blank use middle. Then sort on that new field.

This is going to make indexes fairly useless so performance will be bad for large data sets.

A better way would be to correct your data and never save with a blank last name. If last name is blank then move middle name to last name.

Upvotes: 1

Related Questions