Ordering fields from find query with projection

Question

I have a Mongo find query that works well to extract specific fields from a large document like...

db.profiles.find(
  { "profile.ModelID" : 'LZ241M4' },
  {
    _id : 0,
    "profile.ModelID" : 1,
    "profile.AVersion" : 2,
    "profile.SVersion" : 3
  }
);

...this produces the following output. Note how the SVersion comes before the AVersion in the document even though my projection asked for AVersion before SVersion.

{ "profile" : { "ModelID" : "LZ241M4", "SVersion" : "3.5", "AVersion" : "4.0.3" } }
{ "profile" : { "ModelID" : "LZ241M4", "SVersion" : "4.0", "AVersion" : "4.0.3" } }

...the problem is that I want the output to be...

{ "profile" : { "ModelID" : "LZ241M4", "AVersion" : "4.0.3", "SVersion" : "3.5" } }
{ "profile" : { "ModelID" : "LZ241M4", "AVersion" : "4.0.3", "SVersion" : "4.0" } }

What do I have to do get the Mongo JavaScript shell to present the results of my query in the field order that I specify?

My query is up there. On one line its: db.profiles.find( { "profile.ModelID" : 'LZ241M4' }, { _id : 0, "profile.ModelID" : 1, "profile.AVersion" : 2, "profile.SVersion" : 3 } ); I haven't considered sort(), but that sorts the "rows", I am trying to sort the "columns". — Bob Kuhar, Aug 15 '12 at 23:33
Sorry, I don't know how I missed that. I think you already know but there is such thing as "columns" in mongodb. Is this your whole document or is profile a subset of larger document? — jamjam, Aug 16 '12 at 00:00
"profile" is a sub-document of a larger document. I put the quotes around "rows" and "columns" because I know that Mongo is all about documents and fields, but for the purpose of this conversation "fields" and "columns" are conceptually similar. My projection asked for the "fields" in the order { _id : 0, "profile.ModelID" : 1, "profile.AVersion" : 2, "profile.SVersion" : 3 } but the Mongo JavaScript shell does not produce the output for each document that way. I am searching for a way to rearrange this output per document. Thanks for your help — Bob Kuhar, Aug 16 '12 at 00:33

Gulnur · Answer 1 · 2017-11-16T20:53:33.047

10

I have achieved it by projecting the fields using aliases, instead of including and excluding by 0 and 1s. Try this:

{
_id : 0,      
"profile.ModelID" :"$profile.ModelID", 
"profile.AVersion":"$profile.AVersion",
"profile.SVersion":"$profile.SVersion"
}

edited Nov 16 '17 at 20:53

answered Nov 16 '17 at 20:30

Gulnur

101
1
3

This should be the valid selected answer. Thanks @Gulnur! – Gabriel R. Nov 23 '20 at 17:27

jamjam · Accepted Answer · 2012-08-16T01:09:52.360

7

I get it now. You want to return results ordered by "fields" rather the value of a fields.

Simple answer is that you can't do this. Maybe its possible with the new aggregation framework. But this seems overkill just to order fields.

The second object in a find query is for including or excluding returned fields not for ordering them.

  {
    _id : 0,               // 0 means exclude this field from results
    "profile.ModelID" : 1, // 1 means include this field in the results
    "profile.AVersion" :2, // 2 means nothing
    "profile.SVersion" :3, // 3 means nothing
  }

Last point, you shouldn't need to do this, who cares what order the fields come-back in. You application should be able to make use of the fields it needs regardless of the order the fields are in.

edited Aug 16 '12 at 01:09

answered Aug 16 '12 at 00:54

jamjam

3,171
7
34
39

3

I don't share your opinion that its "overkill" to have the fields returned in the order the projection recommends, but its mongo; i've gotten used to disappointment. Thanks for your help. – Bob Kuhar Aug 16 '12 at 15:33
1

So both the "ordered" and "unordered" version represent the same data. In SQL, the column order may be important for things like `DataReader` where you pick off a column by number. But with MongoDB you are effectively getting back a hash table. There's no ordering feature, because the key order of a hash table is not supposed to be relevant. However, I do like the proposal that the inclusion number could be an order. That's definitely worthy of a JIRA ticket. (http://jira.mongodb.org/) – Gates VP Aug 16 '12 at 16:13
1

I havnt tested this, but judging by the output @Bob got, the "2" and "3" did mean something: they were equivalent to using "1". ie: zero means don't include, non-zero means include. – drevicko Dec 15 '12 at 02:47
7

From a practical standpoint, and purely as a personal preference, it would be helpful to be able to order the output fields when using the mongodb shell during development or debugging. I don't *always* want to see the `_id` field as the first output column when I do a `find()`. Yeah, I *may* want to see `_id` but I'm more interested in other fields first, so *always* seeing `_id` first when it's included can be considered noise. – Inactivist May 05 '13 at 14:02
2

@GatesVP: an obvious use case of field order being relevant is displaying the results of `find()` in the mongo shell. I've filed a ticket for that - https://jira.mongodb.org/browse/SERVER-12599 – Dan Dascalescu Feb 04 '14 at 15:25
@DanDascalescu I see where you are coming from, but that's purely a display issue and purely in the shell. Trying to get pretty displays out of the shell is really not an easy concept. Especially with nested documents and other things. – Gates VP Feb 06 '14 at 19:54

score 1 · Answer 3 · answered Sep 16 '20 at 14:18

Another solution I applied to achieve this is the following:

db.profiles
  .find({ "profile.ModelID" : 'LZ241M4' })
  .toArray()
  .map(doc => ({
    profile: {
      ModelID: doc.profile.ModelID,
      AVersion: doc.profile.AVersion,
      SVersion: doc.profile.SVersion
    }
  }))

score 0 · Answer 4 · answered Mar 22 '19 at 08:07

0

Since version 2.6 (that came out in 2014) MongoDB preserves the order of the document fields following the write operation (source).

P.S. If you are using Python you might find this interesting.

answered Mar 22 '19 at 08:07

K.N.

1
1

Ordering fields from find query with projection

4 Answers4

Linked