Date equality in Data API

I have documents that I can find using the Data API.

> % curl --silent --location --request POST 'https://data.mongodb-api.com/app/data-ppqjw/endpoint/data/beta/action/find'   --header 'Content-Type: application/json'   --header 'Access-Control-Request-Headers: *'   --header 'api-key: <api-key>'   --data-raw '{"collection":"db-stats-historical","database":"jdi-admin","dataSource":"Cluster0","filter": { "State": "CA", "County": "Siskiyou", "Race": "White", "Age": "15-24", "Gender": "Female" }, "limit": 100}' | jq .
{
  "documents": [
    {
      "_id": "626303f2452680844dfb8a77",
      "Date": "2020-02-17T00:00:00.000Z",
      "Population": 0,
      "State": "CA",
      "County": "Siskiyou",
    },  {
      "_id": "626303f2452680844dfba711",
      "Date": "2020-02-17T00:00:00.000Z",
      "Population": 0,
      "State": "CA",
      "County": "Siskiyou",
    },...
  ]
}

So, in order to fetch for this data, I have to do a filter like:

{ 
	"State": "CA",
	"County": "Siskiyou",
	"Date": { "$date": {"$numberLong": "1581897600000" }
}

So we think of the dates above as being like “2020-02-17”. But then we see “2020-02-17T00:00:00.000Z” in the database? And then we have to translate that to time-millis to do a query that retrieves this same document? Really?

Oh, yes. And we have to translate “2020-02-17” in PST to “2020-02-16 16:00”. Or is it “2020-02-16 17:00”. And then translate that to time-millis? :–)

Is there a better way to filter on date values in the Data API?

I am not yet familiar with the Data API but I would like to suggest dateFromString as I feel it could work in this case to replace the ugly $date:$numberLong combination.

Thanks for the suggestion. But if I replace:

"Date": { "$date": {"$numberLong": "1581897600000" }

with:

"Date": { "$dateFromString": { "dateString": "2020-02-17" }

then I get:

Failed to find documents: FunctionError: (BadValue) unknown operator: $dateFromString

You are probably heading me in the right direction, but there is still something missing.

Tried all of these. No joy.

"filter": { "Date": "ISODate(\"2020-02-17T00:00:00.000Z\")"}
{"documents":[]

"filter": { "Date": "ISODate(\"2020-02-17T00:00:00.000\")"}
{"documents":[]}

"filter": { "Date": "new ISODate(\"2020-02-17\")"}
{"documents":[]}

"filter": { "Date": "new ISODate(\"2020-02-1700:00:00.000Z\")"}
{"documents":[]}

Try inside $expr such as

{ $expr : { "$eq" : [ "$date" , { "$dateFromString" : { "dateString": "2020-02-17" } }]}}
$ curl --silent --location --request POST 'https://data.mongodb-api.com/app/data-ppqjw/endpoint/data/beta/action/find' --header 'Content-Type: application/json' --header 'Access-Control-Request-Headers: *' --header 'api-key: <api-key>' --data-raw '{"collection":"db-stats-historical","database":"jdi-admin","dataSource":"Cluster0","filter": { "State": "CA", "County": "Siskiyou", "Date": { $expr : { "$eq" : [ "$date" , { "$dateFromString" : { "dateString": "2020-02-17" }}]}}}}'
Invalid syntax error: TypeError: invalid character '$'
$ 
$ curl --silent --location --request POST 'https://data.mongodb-api.com/app/data-ppqjw/endpoint/data/beta/action/find' --header 'Content-Type: application/json' --header 'Access-Control-Request-Headers: *' --header 'api-key: <api-key>' --data-raw '{"collection":"db-stats-historical","database":"jdi-admin","dataSource":"Cluster0","filter": { "State": "CA", "County": "Siskiyou", "Date": { "$expr" : { "$eq" : [ "$date" , { "$dateFromString" : { "dateString": "2020-02-17" }}]}}}}'
Failed to find documents: FunctionError: (BadValue) unknown operator: $expr
$ 

FYI, I am seeing in the doc for the Data API that it uses canonical MongoDB Extended JSON, not relaxed.

The numberLong may be the only approach that works with canonical. No?

I have verified that I can start working on my application without the ability to filter on dates. But I am going to be shipping a lot more data around and filtering it out in my app. If I understand things, this translates to more charges, yes? Or does it? I am not a money person.

Hi @Ray_Kiddy - Welcome to the community :slight_smile:

Would the following filter example work for your use case?:

"filter": 
{ 
"State": "CA",
"County": "Siskiyou", 
"Date":"2020-02-17T00:00:00.000Z"
}

Please note the date and time value specified above is in ISO-8601 format which is the standard date time format in the form of YYYY-MM-DDTHH:MM:SS.SSSZ with the Z denoting Zulu time (UTC).

I was able to retrieve the two following documents using the above filter and the Data API on my test environment:

{"documents":[
{"_id":"626303f2452680844dfb8a77","Date":"2020-02-17T00:00:00.000Z","Population":0,"State":"CA","County":"Siskiyou"},
{"_id":"626303f2452680844dfba711","Date":"2020-02-17T00:00:00.000Z","Population":0,"State":"CA","County":"Siskiyou"}
]}

MongoDB stores times in UTC, where a couple of advantages are no daylight saving changes and unambiguity. Typically there are helpers for most languages to deal with timezone conversions such as moment.js for Javascript.

I have verified that I can start working on my application without the ability to filter on dates. But I am going to be shipping a lot more data around and filtering it out in my app. If I understand things, this translates to more charges, yes? Or does it? I am not a money person.

Just to clarify regarding to the “more charges” you’ve mentioned, are you referring to retrieving all the documents (including documents you do not require) using the Data API and then filtering out the ones you do not need on the application end in comparison to retrieving just the documents you need using the Data API?

Please note that the Data API is currently in preview and things may change in the future.

Regards,
Jason

I am extremely surprised that this works. But since

may be I should not be.

This means the Data API does automatic type conversion from string to date or date to string. Querying a date field with a string value usually does not work without automatic type conversion which I am accustomed to.

Using mongosh:

> date_as_string = "1965-04-17T00:00:00.000Z"
< '1965-04-17T00:00:00.000Z'
> date_as_date = new Date( date_as_string )
< 1965-04-17T00:00:00.000Z
> c.insertOne( { "_id" : "date_as_date" , date : date_as_date })
< { acknowledged: true, insertedId: 'date_as_date' }
> c.insertOne( { "_id" : "date_as_string" , date : date_as_string })
< { acknowledged: true, insertedId: 'date_as_string' }

// let see both documents
> c.find()
< { _id: 'date_as_date', date: 1965-04-17T00:00:00.000Z }
< { _id: 'date_as_string', date: '1965-04-17T00:00:00.000Z' }

// searching with date_as_string only finds the document that has the string typed date field
> c.find( { date : date_as_string })
< { _id: 'date_as_string', date: '1965-04-17T00:00:00.000Z' }

// searching with date_as_date only finds the documents that has the date typed date field
> c.find( { date : date_as_date })
< { _id: 'date_as_date', date: 1965-04-17T00:00:00.000Z }

When I see the following in your result set, it makes me believe your dates are stored as string in your collection. Unless the Data API does automatic type conversion.

Were it so simple. Here is an object in the db, in all its glory:

{
      "_id": "626537362312aa48b3636ee0",
      "Date": "2020-02-17T00:00:00.000Z",
      "Race": "White",
      "Age": "15-24",
      "Gender": "Female",
      "Classification": "Felony",
      "Population": 0,
      "State": "CA",
      "County": "Siskiyou"
    }

Here is a query.


$ curl --silent --location --request POST 'https://data.mongodb-api.com/app/data-ppqjw/endpoint/data/beta/action/find' --header 'Content-Type: application/json' --header 'Access-Control-Request-Headers: *' --header 'api-key: <api-key>' --data-raw '{"collection":"db-stats-historical","database":"jdi-admin","dataSource":"Cluster0","filter": { "State": "CA", "County": "Siskiyou", "Race": "White", "Age": "15-24", "Date": "2020-02-17T00:00:00.000Z" }}'

{"documents":[]}
$

When I see the following in your result set, it makes me believe your dates are stored as string in your collection.

You are correct here @steevej - Apologies as there was the incorrect data type for the Date field in my test environment.

I have the following inserted the new data in my test database as shown in the mongosh output:

apidb> db.apicoll.find()
[
  {
    _id: ObjectId("6268753831b847b84779d7e9"),
    Date: ISODate("2020-02-17T00:00:00.000Z"),
    Population: 0,
    State: 'CA',
    County: 'Siskiyou'
  },
  {
    _id: ObjectId("6268753831b847b84779d7ea"),
    Date: ISODate("2020-02-17T00:00:00.000Z"),
    Population: 0,
    State: 'CA',
    County: 'Siskiyou'
  },
  {
    _id: ObjectId("6268753831b847b84779d7eb"),
    Date: ISODate("2020-02-19T00:00:00.000Z"),
    Population: 0,
    State: 'CA',
    County: 'Siskiyou'
  }
]
Atlas atlas-8l5jrq-shard-0 [primary] apidb>

Running the Data API with the following filter against the test data above:

"filter": 
{
"State": "CA",
"County": "Siskiyou",
"Date":{"$date":"2020-02-17T00:00:00.000Z"}
}

Returns the following:

{"documents":[
{"_id":"6268753831b847b84779d7e9","Date":"2020-02-17T00:00:00.000Z","Population":0,"State":"CA","County":"Siskiyou"},
{"_id":"6268753831b847b84779d7ea","Date":"2020-02-17T00:00:00.000Z","Population":0,"State":"CA","County":"Siskiyou"}
]}

@Ray_Kiddy - Would you be able to try the above filter to see if that works?

Additionally, could you provide the following:

  1. The output from mongosh when you run the following command (replacing “collection” with the collection name where the sample documents you have provided exists):
db.collection.find({ "State": "CA", "County": "Siskiyou", "Date": ISODate("2020-02-17T00:00:00.000Z")})
  1. If the returned documents from the above mongosh command are the expected documents you wish to be returned when using the Data API

Regards,
Jason

3 Likes