MongoDB: adding sort() to query ruins result set for $and query

Question

I've stumbled upon some very strange behavior with MongoDB. For my test case, I have an MongoDB collection with 9 documents. All documents have the exact same structure, including the fields expired_at: Date and location: [lng, lat].

I now need to find all documents that are not expired yet and are within a bounding box; I show match documents on map. for this I set up the following queries:

var qExpiry = {"expired_at": { $gt : new Date() } };
var qLocation = { "location" : { $geoWithin : { $box : [ [ 123.8766, 8.3269 ] , [ 122.8122, 8.24974 ] ] } } };
var qFull = { $and: [ qExpiry, qLocation ] };

Since the expiry date is long in the past, and when I set the bounding box large enough, the following queries give me all 9 documents as expected:

db.docs.find(qExpiry);
db.docs.find(qLocation);
db.docs.find(qFull);
db.docs.find(qExpiry).sort({"created_at" : -1});
db.docs.find(qLocation).sort({"created_at" : -1});

Now here's the deal: The following query returns 0 documents:

db.docs.find(qFull).sort({"created_at" : -1});

Just adding sort to the AND query ruins the result (please note that I want to sort since I also have a limit in order to avoid cluttering the map on larger scales). Sorting by other fields yield the same empty result. What's going on here?

(Actually even stranger: When I zoom into my map, I sometimes get results for qFull, even with sorting. One could argue that qLocation is faulty. But when I only use qLocation, the results are always correct. And qExpiry is always true for all documents anyway)

Have you tried running the same query using the aggregation framework's $match and $sort pipelines? — chridam
– chridam, Commented Feb 17, 2017 at 9:59
@chridam -- wow, that did the trick! I will provide an answer since this seems something others might encounter as well. Any idea why this way worked and my initial approach not? — Christian
– Christian, Commented Feb 17, 2017 at 10:17

chridam · Accepted Answer · 2017-02-17 10:25:59Z

2

You may want to try running the same query using the aggregation framework's $match and $sort pipelines:

db.docs.aggregate([
    { "$match": qFull },
    { "$sort": { "created_at": -1 } }
]);

or implicitly using $and by specifiying a comma-separated list of expressions as in

db.docs.aggregate([
    { 
        "$match": {
            "expired_at": { "$gt" : new Date() },
            "location" : { 
                "$geoWithin" : { 
                    "$box" : [ 
                        [ 123.8766, 8.3269 ], 
                        [ 122.8122, 8.24974 ] 
                    ] 
                } 
            }
        } 
    },
    { "$sort": { "created_at": -1 } }
]);

Not really sure why that fails with find()

answered Feb 17, 2017 at 10:25

chridam

104k26 gold badges246 silver badges243 bronze badges

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

Comments

Christian · Accepted Answer · 2017-02-17 10:25:27Z

1

chridam suggestion using the aggregation framework of MongoDB proved to be the way to go. My working query now looks like this:

db.docs.aggregate(
    [ 
      { $match : { $and : [qExpiry, qLocation]} },
      { $sort: {"created_at": -1} }.
      { $limit: 50 }.
    ]
);

Nevertheless, if any can point out way my first approach did not work, that would be very useful. Simply adding sort() to a non-empty query shouldn't suddenly return 0 documents. Just to add, since I still tried for a bit, .sort({}) return all documents but was not very useful. Everything else failed including .sort({'_id': 1}).

answered Feb 17, 2017 at 10:25

Christian

3,4237 gold badges46 silver badges85 bronze badges

Collectives™ on Stack Overflow

MongoDB: adding sort() to query ruins result set for $and query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related