Problem with valid consultation in mongodb

[alexfsil]

Hi Jeroen,

When I run this:

db.system.profile.find({ 
  "timestamp" : { 
    $lt: new Date(), 
    $gte: new Date(new Date().setDate(new Date().getDate()-1))
  }   
})

On mongo I get the data for the last 24 hours.

However, when I run the query in R:

library(mongolite)

con <- mongolite::mongo(collection = "test", db = "test", url = "mongodb://localhost")

con$find('{ 
  "Timestamp" : { 
    $lt: new Date(), 
    $gte: new Date(new Date().setDate(new Date().getDate()-1))
  }   
}')

I get the following error:

Error: Invalid JSON object

Could you fix it?

[jeroen]

mongolite requires that you use proper json format for your query, i.e. use double-quotes around the keys, and no inline javascript.

[alexfsil]

I just tried using double quotes and it returns the same problem... con$find(paste0('{ "Timestamp" : { $lt: new Date(), $gte: new Date(new Date().setDate(new Date().getDate()-1))}}'))

However if I use this query (with single quotes):

con$find(
  query = paste0('{"Timestamp":{"$gte": { "$date" : { "$numberLong" : "', d, '" } } },
                 "$or": [ { "FeederName": null }, { "FeederName": "" } ]}'),
  fields = '{"MasterId":true, "Latitude": true, "Longitude":true}')

works perfectly. Are you sure the problem is there?

[jeroen]

Yes you also need to use quotes around $lt: and $gte and you cannot use inline javascript, you need to generate the date string in R using for example Sys.time().

You can use jsonlite::validate(query) to test if your query is proper json or you can check it on https://jsonlint.com/ or any other json validator.

[alexfsil]

If I use Sys.time() the problem lies in the UTC format, I'll explain. Using:

date <- as.POSIXlt(Sys.time(), tz = "UTC") [1] "2020-06-15 05:20:44 UTC" date_p <- date- 86400 [1] "2020-06-14 05:20:44 UTC" d <- as.integer(date - 86400 ) * 1000

Master_T <- con$find( query = paste0('{"Timestamp":{"$gte": { "$date" : { "$numberLong" : "', d, '" } } }, "$or": [ { "FeederName": null }, { "FeederName": "" } ]}'), fields = '{"MasterId":true, "BaseStation":true, "Timestamp":true}')

Comparing the difference between the minimum and maximum date, the result is not 24 hours:

max(Master_T$timestamp)-min(Master_T$timestamp) Time difference of 22.61611 hours

Could you check if it recognizes the UTC format correctly?