[SERVER-18163] Documents sorted in reverse order Created: 22/Apr/15  Updated: 30/Jul/15  Resolved: 22/Apr/15

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: 3.0.2
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Matt Parlane Assignee: David Storch
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
Operating System: ALL
Steps To Reproduce:

db.test.save({data:[{f:1,v:'Adam'}]});
db.test.save({data:[{f:1,v:'Bob'}]});
db.test.save({data:[{f:1,v:'Charles'}]});
 
db.test.aggregate([
  { $match: { 'data.f': 1 } },
  { $unwind: '$data' },
  { $sort: { 'data.v': 1 } },
  { $group: {
      _id: '$_id',
      data: {
        $push: { f: '$data.f', v: '$data.v' }
      }
    }
  }
]);

Participants:

 Description   

The code below produces this:

{ "_id" : ObjectId("5537436ea93a16b4e1d4ad17"), "data" : [ { "f" : 1, "v" : "Charles" } ] }
{ "_id" : ObjectId("5537436ea93a16b4e1d4ad16"), "data" : [ { "f" : 1, "v" : "Bob" } ] }
{ "_id" : ObjectId("5537436ea93a16b4e1d4ad15"), "data" : [ { "f" : 1, "v" : "Adam" } ] }

But it seems to me it should be the reverse. Changing the 1 to -1 in the $sort seems to reverse it again so it's in alphabetical order.

Oddly, the phenomenon is not always consistent – it seems to be consistent when run via the console, but when I run the equivalent code via either PHP or Ruby, the records are sometimes ordered CAB or ACB etc.

Tested on both WT and MMAPv1.



 Comments   
Comment by David Storch [ 22/Apr/15 ]

Hi matt.parlane@gmail.com,

How would I then search on field 1 but sort by field 2?

The predicate language and aggregation certainly don't make this easy. I'd say your best bet is to do the $sort after the $unwind and omit the $group stage altogether. This means that you'll only get back the f:2 array elements, but otherwise I think it's what you need.

> db.test.aggregate([
  { $match: { 'data.f': 1 } },
  { $unwind: '$data' },
  { $match: { 'data.f': 2 } },
  { $sort: { 'data.v': 1 } },
])
{ "_id" : ObjectId("55376d31c9dbeb72fe1841a9"), "data" : { "f" : 2, "v" : "Adams" } }
{ "_id" : ObjectId("55376d30c9dbeb72fe1841a7"), "data" : { "f" : 2, "v" : "Jones" } }
{ "_id" : ObjectId("55376d30c9dbeb72fe1841a8"), "data" : { "f" : 2, "v" : "Smith" } }

Let's move further discussion to the MongoDB user group post linked above. The SERVER project is primarily a forum for bug reports and feature requests, so this kind of question is better suited to the user group.

Best,
Dave

Comment by Matt Parlane [ 22/Apr/15 ]

Thanks for that, and sorry for wasting your time.

My problem is when the situation becomes more complex, by adding more dynamic fields. For example, if the surname field had id 2, you would have this data:

db.test.save({data:[{f:1,v:'Adam'},{f:2,v:'Jones'}]});
db.test.save({data:[{f:1,v:'Bob'},{f:2,v:'Smith'}]});
db.test.save({data:[{f:1,v:'Charles'},{f:2,v:'Adams'}]});

How would I then search on field 1 but sort by field 2? I've got the searching part figured out, but I'm trying to do the sorting. I'm working from Asya's article here: http://askasya.com/post/dynamicattributes and in the comments she talks about using the aggregation framework to do the sorting, and I've tried to follow her very rough instructions.

Cheers,

Matt

Comment by David Storch [ 22/Apr/15 ]

Hi matt.parlane@gmail.com,

Thanks for reporting this issue. It looks like the output of the aggregation command isn't sorted as you expected because the $sort stage is not the last one in the pipeline. The sorted results are fed into the $group pipeline stage, which makes no guarantees about the order in which it outputs the groups. Perhaps you mean to put the $sort stage after the $group stage, as in the shell snippet below?

> db.test.drop();
> db.test.save({data:[{f:1,v:'Adam'}]});
> db.test.save({data:[{f:1,v:'Charles'}]});
> db.test.save({data:[{f:1,v:'Bob'}]});
> db.test.aggregate([
...   { $match: { 'data.f': 1 } },
...   { $unwind: '$data' },
...   { $group: {
...       _id: '$_id',
...       data: {
...         $push: { f: '$data.f', v: '$data.v' }
...       }
...     }
...   },
...   { $sort: { 'data.v': 1 } },
... ]);
{ "_id" : ObjectId("55374deac9dbeb72fe18419e"), "data" : [ { "f" : 1, "v" : "Adam" } ] }
{ "_id" : ObjectId("55374deac9dbeb72fe1841a0"), "data" : [ { "f" : 1, "v" : "Bob" } ] }
{ "_id" : ObjectId("55374deac9dbeb72fe18419f"), "data" : [ { "f" : 1, "v" : "Charles" } ] }

I know this is a toy example, but note that in this case there is no need for the aggregation framework. You can achieve the same result with the following .find():

> db.test.find({'data.f': 1}).sort({'data.v': 1})
{ "_id" : ObjectId("55374deac9dbeb72fe18419e"), "data" : [ { "f" : 1, "v" : "Adam" } ] }
{ "_id" : ObjectId("55374deac9dbeb72fe1841a0"), "data" : [ { "f" : 1, "v" : "Bob" } ] }
{ "_id" : ObjectId("55374deac9dbeb72fe18419f"), "data" : [ { "f" : 1, "v" : "Charles" } ] }

I am closing this ticket as Works as Designed, but feel free to re-open if you have any further concerns.

Best,
Dave

Comment by Matt Parlane [ 22/Apr/15 ]

I should mention that Asya thought it might be a bug: https://groups.google.com/d/topic/mongodb-user/HylTsQCGVd4/discussion

Generated at Thu Feb 08 03:46:45 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.