Query MongoDb for the N most recent records

Questions
#1

When querying a large Mongo database I often only want a subset of documents - typically the N most recently added. The ‘Number of rows’ drop-down box in Exploratory’s Import MongoDB dialogue box allows you to specify 100, 1000, 1000 etc. rows but for my database this happens to return the first and oldest N rows

  1. Is there a way of querying for the say 100 most recent rows?

I have tried:
{“logged_datetime”: {"$gte": ISODate(“2016-10-13 00:00:00”)}} - but I don;t think it likes ISODate(), and;

{"$sort": {“logged_datetime”: -1}} - but I don’t think the query box allows any other operations other than specifying the $match part of the query.

  1. Are there any plans to support parsing full MongoDB pipeline queries e.g.
    db.collection.find({…}).sort({…}).limit(10) or;

aggregate queries e.g.

db.seq_metrics.aggregate([{"$match": {“logged_datetime”: {"$gte": ISODate(“2016-10-13 00:00:00”)}}},
{"$sort": {"_id": 1}},
{"$project": {"_id": 0,
“IP”: “$remote_addr”,
“logged_datetime”: “$logged”}}])

Many thanks,
Henry

#2

Hi Henry,

As to ISODate, it could be that it’s expecting a normal JSON convension, so you might want to add single quotes like

‘ISODate(“2016-10-13 00:00:00”)’

I haven’t tested this, so not 100% sure. We’ll check this here as well.

As to other verbs, we’re going to add a support of sort, limit and other parameters for the query operation and support the aggregate operation as well. Basically, we’ll add a full support of ‘find’ and ‘aggregate’ functions, which you can find in this doc.

Will that work for you?

#3

Hi Henry,
About sorting, I believe a query like the following would sort your result in descending order.

{"$query": {}, "$orderby": {"logged_datetime" : -1}}

About querying with date, something like the following should work.

{"logged_datetime":{"$gt": { "$date" : "2016-10-13T00:00:00Z" } } }

In general, we use the “strict mode” specified here as the query language, which requires using those extensions like $date instead of calling functions like ISODate().
https://docs.mongodb.com/manual/reference/mongodb-extended-json/#data_date

#4

Hi Hideaki,

Thanks for the suggestions.

This one worked (gave me the most recent records first)

{"$query": {}, "$orderby": {"logged_datetime" : -1}}

This one:

{"logged_datetime":{"$gt": { "$date" : "2016-10-13T00:00:00Z" } } }

worked on one collections (‘collection_1’) but on another (larger) collection (‘collection_2’) it did not It reported:

Error
invalid response (expecting SEXP)

The date time filtered code didn’t work in 3T MongoChef (Core 4.2.0) on either collection :
db.collection_1.find({“logged”:{"$gt": { “$date” : “2016-10-16T00:00:00Z” } } }).limit(2)
db.collection_2.find({“logged”:{"$gt": { “$date” : “2016-10-16T00:00:00Z” } } }).limit(2)

  • it returns 0 documents.

(BTW:
I’m still investigating a third error that only happens on collection_3 when I use:

{"$query": {}, "$orderby": {"logged_datetime" : -1}}

It returns error

Error
long packets are not implemented
)

There are probably a few issues going on here - some of which may relate to me!

Anyway $orderby is a good solution for now.

Many thanks,
Henry