Mongodb sorting order

Question

Is there a way to specify the order of keys to sort by?

There seems to be some confusion in the comments. To be clear, I'm not asking about ascending or descending order. I'm asking if I can specify sorting priority.

For example, I want to sort by:

pinned (Boolean)
score (Number)
newest (Date or ObjectId)

so that

only docs with the same pinned value get sorted by score, and
only docs with the same pinned and score get sorted by newest?

In other words,

score, newest should never be considered if pinned value is different (just sort by pinned)
newest should never be considered if score is different.

For example,

{ pinned: false, score:1, _id: new }
{ pinned: true, score:1, _id: oldest }
{ pinned: false, score:2, _id: old }

should be ordered as

{ pinned: true,  score: 1, _id: oldest }
{ pinned: false, score: 1, _id: new }
{ pinned: false, score: 2, _id: old }

IMHO, it would be easier for people to reply if you put some sample data... — Askar
– Askar, Commented Apr 25, 2014 at 2:41
I didn't think the question was that complex, but I've added an example. — az_
– az_, Commented Apr 25, 2014 at 2:45
are you using the mongo shell for your testing? In mongo order is significant in objects in several places. Sort clause is one of them. Index specification is another. — Asya Kamsky
– Asya Kamsky, Commented Apr 25, 2014 at 4:21
@AsyaKamsky can you make that an answer? It's the only response that gets to the root of the solution (that mongodb doesn't follow Ecmascript) — az_
– az_, Commented Apr 25, 2014 at 4:34
According to JSON spec yes it should be an unordered collection. However, here the order does matter. — yaoxing
– yaoxing, Commented Apr 25, 2014 at 4:35

Asya Kamsky · Accepted Answer · 2014-04-25 05:07:06Z

2

While JSON documents are unordered, in MongoDB there are several places where order matters. The most important ones to remember are:

sort order
index specification order

When you specify a sort, the sort order will follow the order of fields, so in your case it would be .sort({pinned:1,score:1,newest:1}) you can see an example in the documentation.

Some operations expect fields in particular order.

It also matters for subdocuments if you are trying to match them (whereas top level field order in the query does not matter).

answered Apr 25, 2014 at 5:07

Asya Kamsky

42.4k5 gold badges113 silver badges133 bronze badges

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

4 Comments

Dmitri Zaitsev Over a year ago

How can MongoDB distinguish between the identical objects {score:1,newest:1} and {newest:1,score:1}

Asya Kamsky Over a year ago

They are not identical in their bson representation at all. Look at them as strings, for example - not at all identical and have a clearly defined sort order.

Dmitri Zaitsev Over a year ago

Their string (or bson) representations are not identical but as JavaScript objects - they are indistinguishable, as the key order is not guaranteed. So if I do .sort(obj) with JS object obj inside, I don't see how to reliably declare the order between the fields.

Dmitri Zaitsev Over a year ago

BTW I find Mongo's description of Bson vs Json very confusing and obscure: mongodb.com/json-and-bson is saying "MongoDB represents JSON documents in binary-encoded format called BSON behind the scenes". So is Bson only used behind the scene?

yaoxing · Accepted Answer · 2014-04-25 04:43:19Z

1

So I inserted some sample data as is in your question with mongo shell:

db.test.insert({ pinned: false, score:1, _id: "new" });
db.test.insert({ pinned: true, score:1, _id: "oldest" });
db.test.insert({ pinned: false, score:2, _id: "old" });

Try to sort it by:

db.test.find().sort({pinned: -1, score: 1, _id: 1})

It turns out the result is:

{ "_id" : "oldest", "pinned" : true, "score" : 1 }
{ "_id" : "new", "pinned" : false, "score" : 1 }
{ "_id" : "old", "pinned" : false, "score" : 2 }

Isn't that what you want?

EDIT: Keep in mind that JavaScript doesn't strictly obey JSON spec. e.g. JSON spec also says property names should quoted with ", JavaScript however you don't have to quote, or you can quote with '.

edited Apr 25, 2014 at 4:43

answered Apr 25, 2014 at 4:30

yaoxing

4,2332 gold badges24 silver badges33 bronze badges

4 Comments

az_ Over a year ago

Thanks for your responses above and here! It turns out that I had a typo in my sort query - and so it was ignoring both score and _id. I really appreciate your help, but next time, it'd be a lot more helpful if you explained the "why" along with the "what". edit: and I see you added it right before I posted this comment. :+1:

yaoxing Over a year ago

I'd like to explain why over what. I just didn't know which part confused you.

az_ Over a year ago

Actually, it's specifically stated as unordered in both JSON and ECMAScript. I wonder why the MongoDB devs decided to ignore it.

yaoxing Over a year ago

It's not mongodb doesn't want to obey the spec, it's JavaScript doesn't. And mongo shell is based on JavaScript V8 engine. I added another example in my answer. I though people using JavaScript know about this trick, and didn't know you stuck there.

Collectives™ on Stack Overflow

Mongodb sorting order

2 Answers 2

4 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related