Mongo Aggregation multiple sorting stages (nodejs)

Question

I am trying to build complex aggregation pipeline, which will fetch items from collection and sort them multiple times.

What I need is to: 1) get all users from collection 2) sort them by user-selected value (f.e. by amount of comments they left) 3) sort them by role (admin, moderator, basic-user)

Idea is to show admin user first even if some basic user have more comments, but if we have 2 admins - they would be sorted too.

Note that first $sort is to avoid unexpected sort+limit behavior, when items aren't fetched in constant order. Value of $skip is calculated while building pipeline depending on page number passed in API request.

For now I have something like this:

[
  { $match: <some condition> },
  { $sort: { _id: 1 } },
  { $sort: { countOfComment: -1 } },
  { $sort: { isAdmin: -1, isModerator: -1 } },
  { $skip: <page * 10> },
  { $limit: 10 }
]

If I got it right - it should: 1) get all users sorted by id, so there would be no duplications in different pages. this requires unique field to work 2) sort by amount of comments 3) sort by user role. It meant to save previous sorting results, so

- user with 4 comments
- moderator with 0 comments
- admin with 3 comments
- user with 2 comments
- admin with 1 comment

would become

- admin with 3 comments
- admin with 1 comment
- moderator with 0 comments
- user with 4 comments
- user with 2 comments

But it doesn't. Looks like each next $sort statement loses results of previous one. Any ideas how to reach desired behavior?

Merge all the $sort stages into one { $sort: { countOfComment: 1, isAdmin: -1, isModerator: -1 } }, — Ashh
– Ashh, Commented Jan 23, 2019 at 7:17
@AnthonyWinzlet I've tried it with no luck, but I think I should give it another chance and play with order of items. — Elagin Vladislav
– Elagin Vladislav, Commented Jan 23, 2019 at 7:36

Elvis · Accepted Answer · 2019-01-23 07:28:41Z

1

You only need one sort stage, and that is,

    $sort: {
      isAdmin: -1,
      isModerator: -1,
      countOfComment: -1
    }

Basically, since you've sorted by countOfComment first, then the highest priority will be given to sorting by countOfComment. So, no matter what, the countOfComment field will definitely be sorted in descending order and only then will it see the next field to sort. Similarly, the last field will get the least priority while sorting.

The order in which you place the sort items is the order of priority.

answered Jan 23, 2019 at 7:28

Elvis

1,1438 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Elagin Vladislav Over a year ago

Does it mean that I can still keep multiple $sort stages, but reorder them? Anyway, this answer helped me so I will check it as right.

Elagin Vladislav Over a year ago

Another question is that I've read that JS objects aren't ordered. It works, but can I actually relate on order of key:value pairs there?

VIKAS KOHLI · Accepted Answer · 2019-01-23 07:37:51Z

1

You can merge sort in one object like:-

{ $sort: { countOfComment: 1, isAdmin: -1, isModerator: -1 } }

You can use $group for removing duplicate records and then use project and after that use the sort

edited Jan 23, 2019 at 7:37

answered Jan 23, 2019 at 7:30

VIKAS KOHLI

8,4904 gold badges52 silver badges67 bronze badges

3 Comments

Elagin Vladislav Over a year ago

Thanks for your answer! But what do you mean by 'duplicate records'?

VIKAS KOHLI Over a year ago

You're saying there should be no duplication records in the pagination, then that's why I recommend you to use $group

Elagin Vladislav Over a year ago

I think I used term 'duplications' in wrong way. It looked that with same sorting parameters page N and page N+1 can contain few similar listings.

Collectives™ on Stack Overflow

Mongo Aggregation multiple sorting stages (nodejs)

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related