CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Cédric
Cédric

Reputation: 827

Mongodb: Making a field the result of an operation in a query

How to include fields operations in query with MongoDB ?

My documents contain a 'start_time' and a 'duration' fields, and I want get all documents that are style active (where 'now' is lower than 'start_time' + 'duration').

The PostgreSQL equivalent is :

SELECT * FROM my_table 
    WHERE start_time + (INTERVAL '1 min' * duration)) > current_timestamp

Is it possible with a simple query in MongoDB or should I use aggregation pipelines to get the same result?

Thanks.

Upvotes: 0

Views: 95

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

Assuming that current_timestamp here is an external variable, then this was not even a good way to represent this in SQL.

The correct way would have been to do the "reverse" that and rather than make the database recalculate the "start_time" value, alter your input value by the interval by subtracting the duration and make the database search for "indexed values" that are "greater than" the time being searched for.

Exactly therefore how you should be doing it with MongoDB, with JavaScript example in lieu of a chosen language. Easy enough to understand:

var duration = 5;   // 5 minutes
var current_timestamp = new Date();

var altered_time = new Date( 
    current_timetamp.valueOf() - ( 1000 * 60 * duration )
);

db.collection.find({ "start_time": { "$gt": altered_time } })

So really, as would be the same with SQL form, doing the comparison that way around is the most optimal way to do it.

For the insane, or if you really did need to compare two fields in a document, then the non-optimal way to do it is with the aggregation pipeline which also cannot use an index to resolve it:

db.collection.aggregate([
    { "$project": {
        "doc": "$$ROOT",
        "matched": {
            "$gt": [
                { "$add": [
                    { "$subtract": [ "$start_time", new Date("1970-01-01") ] },
                    1000 * 60 * duration
                ]},
                current_timestamp.valueOf()
            ]
        }
    }},
    { "$match": { "matched": true } }
])

Not a good way to do that, but neither was the SQL as you wrote it.

So where duration is a field in the current document, then just address differently:

db.collection.aggregate([
    { "$project": {
        "doc": "$$ROOT",
        "matched": {
            "$gt": [
                { "$add": [
                    { "$subtract": [ "$start_time", new Date("1970-01-01") ] },
                    { "$multiply": [ 1000 * 60, "$duration" ] }
                ]},
                current_timestamp.valueOf()
            ]
        }
    }},
    { "$match": { "matched": true } }
])

Upvotes: 1

Related Questions