mongoose sorting documents by a specific values from the document

Question

I'm using this syntax to filter documents

const query = Model.find();
const filters = req.body;
if(filters.someValue){
    query.find().where('someValue').eq(filters.someValie);
}
...(imagine 40 more)

before i execute this query to find all the documents, i want to sort it by a specific order which i will determine

if(filters.sort){
    here the sort need to be applied - before execution.
}

i got some field in the DB with four optional values, and i want to attach order to each value (the sort needs to be applied by that). lets say the values can be:

"A", "B", "C", "D"

the order i want the documents to be sorted by:

{value:"A",order:3}, {value:"B", order:2}, {value:"C", order:4}, {value:"D", order:1}

and after the sort is apply - execute the query. the problem is that i could not find anything about this online, and the only thing i tried (Aggregate with sort and condition) - didn't work. the code:

      SomeModel.aggregate([
    { "$project" : {
      "document": "$$ROOT",
        "order" : {
            "$cond" : {
                if : { "$eq" : ["$someField", "D"] }, then : 1,
                else  : { "$cond" : {
                    "if" : { "$eq" : ["$someField", "B"] }, then : 2, 
                    else  : { "if" : { "$eq" : ["$someField", "A"]}, then: 3, 
                    else : { "if" : { "$eq" : ["$someField", "C"]}, then: 4
                        }
                      }
                    }
                }
            }
        }
    }}, 
    {"$sort" : {"order" : 1} }
]);

you cant actually sort before firing, you need to do it in JavaScript code — Kunal Mukherjee, Apr 26 '20 at 16:17
i did it at first and it was good for most of the cases, the problem is that sometimes the number of documents is more than 1M and than the server crashes with error of no memory stack left.... — Noam Atishkin, Apr 26 '20 at 16:58
You can sort in code (in memory / blocking the event loop) using `.sort` before sending to db — Kunal Mukherjee, Apr 26 '20 at 17:04
and one more problem, i got a limitation of 100 documents per page (i'm also using pagination) — Noam Atishkin, Apr 26 '20 at 17:04
i dont understand what you mean by that, how can this method help me achieve what i want? i need sort specific fields with specific value and order @KunalMukherjee — Noam Atishkin, Apr 26 '20 at 17:13

score 0 · Answer 1 · answered Apr 26 '20 at 17:32

0

It looks like a problem with malformed inner if/then/elses. You are actually missing some $cond operator in third and fourth else. Because of that you are getting incorrect value for order property (an object with if/then/else instead of number).

This version works for me:

[
  {
    $project: {
      'document': '$$ROOT',
      order: {
        $cond: {
          if: {'$eq': ['$someField', 'D']}, then: 1,
          else: {
            '$cond': {
              'if': {'$eq': ['$someField', 'B']}, then: 2,
              else: {
                '$cond': {
                  'if': {'$eq': ['$someField', 'A']}, then: 3,
                  else: {
                    '$cond': {'if': {'$eq': ['$someField', 'C']}, then: 4, else: 5}
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  {$sort: {'order': 1}}
]

However, in this case I think $switch operator might be better, especially if you have more values possible (than just those 4).

aggregate([
  {
    $project: {
      'document': '$$ROOT',
      order: {
        $switch: {
          branches: [
            {case: {'$eq': ['$name', 'D']}, then: 1},
            {case: {'$eq': ['$name', 'B']}, then: 2},
            {case: {'$eq': ['$name', 'A']}, then: 3},
            {case: {'$eq': ['$name', 'C']}, then: 4},
          ],
          default: null
        }
      }
    }
  },
  {$sort: {'order': 1}}
])

answered Apr 26 '20 at 17:32

jjanczyk

481
3
7

First of all, thank you for your time, Second of all - it didn't work (nothing happens when i execute this aggregation) – Noam Atishkin Apr 26 '20 at 18:19
Hmm... could you share some more details then? I ran it on some generated data and it worked fine. Maybe there is something wrong before this step (you said you have some filters before)? Also, make sure to set correct property name, instead of `$name` (which was a property in my fake data). – jjanczyk Apr 26 '20 at 19:07
Of course i changed the $name to my field, the things that i do before is just filtering the things that matches to the query statement (look at the top of my question - i explained what i do. Could you please tell me how it looks when ot works? (i mean, the documents that returned from the exec method were sorted by this aggregation?) – Noam Atishkin Apr 26 '20 at 19:53
Yes, the documents are sorted by `order` in ascending order. The problem actually might be because `find` and `aggregate` don't play well together. Instead of using `find` and then `aggregate` separately, maybe try doing filtering in `aggregate` with `$match` operator (https://docs.mongodb.com/manual/reference/operator/aggregation/match/). See also here for more details: https://stackoverflow.com/questions/42394902/mongoose-how-to-use-aggregate-and-find-together – jjanczyk Apr 27 '20 at 07:48
The problem is the filters are very dynamic (like i said, i got like 40 filters) i need to find a way which i can filter dynamically - and then sort all the documents by the order i want... – Noam Atishkin Apr 27 '20 at 08:52
There shouldn't be a problem of using `$match`, `$project` and `$sort` aggregation operators. With `$match` you can easily build object dynamically. So instead doing series of `query.find().where...`, just expand your object (that would be provided to `$match` as per documentation from comment before) with new clauses. – jjanczyk Apr 27 '20 at 15:05

score 0 · Accepted Answer · answered Apr 30 '20 at 15:02

I managed to fix it by a different method. When i insert documents in the DB - each field i want to index will have another field that will represent the index of the value. (lets say the field name is course and his values are A, B, C, D - i'll add field named courseIndex, and map the index by the value - A = 4 , B = 1, C = 2, D = 3).

With this i can achieve sorting with my conditions :)

mongoose sorting documents by a specific values from the document

2 Answers2