Weired Problem with Date field query returning zero results only using Atlas Data API

Working with Data
,
1

I created a free M0 mongodb cluster in the Atlas Cloud.

I added about 400.000 documents with book data using python MongoClient.
The documents contain a field “publicationDate” of the type “Date” which i defined as follows in python:

pubDateFormatted = pubDate.strftime("%Y-%m-%dT00:00:00Z")
document {
...
"publicationDate": { "$date": pubDateFormatted }
}

Now I am able to query all books from the year 2022 by defining the filter as follows in python and get the correct results:

filter = {
    "type": "pbook",
    "publicationDate": {
        "$gte": {
            "$date": "2022-01-01T00:00:00Z"
        },
        "$lte": {
            "$date": "2022-12-31T23:59:59Z"
        }
    }
}

Now the problem:
I created a small HTML page to display all books. That uses a little php script and the Atlas DATA API to query the data. The API queries work, but as soon as i add the filter for the year it returns zero results.

$filter = array('type' => 'pbook');
if (!empty ($year))
    $filter['pubDate2'] =
        array(
            '$gte' => array(
                '$date' => "2022-01-01T00:00:00Z"
            ),
            '$lte' => array(
                '$date' => "2022-12-31T23:59:59Z"
            )
        );

I also get no results when I run the query in the Atlas admin backend, although the field “publicationDate” is clearly marked with the type “Date” there!

What am I doing wrong?

2

I think that

should be
$filter['publicationDate'] =
since

3

Oh, sorry, i have been testing a lot and copied the wrong code.
I get zero results also when using $filter[‘publicationDate’] = …

Furthermore:
When i query all documents where publicationDate is of type “Date”, i get zero results.
filter = {"publicationDate": {"$type": "date"}}

Even in python, where filtering by date is working, i get zero results for this:

filter = {"publicationDate": {"$type": "date"}}
print(collection.count_documents(filter))
> 0

im really confused.

4

How many documents if you use an empty filter jn

5

I get 380191 results with empty filter

6

can you print out some of the results, a screenshot of the same document in compass would also be nice

7

Thanks for pointing me to Compass. I’m still very new to mongodb.

In Atlast Cloud Backend it says “Date”, in Compass it says “Object”. (Is Date an object? Or did i save it the wrong way?)

image

8

And thats the document:

{
  "_id": {
    "$oid": "64e73f03ed4d31d42c8d7b58"
  },
  "title": "Wie die Stille vor dem Fall. Zweites Buch: Special Edition",
  "isbn": "9783736321670",
  "publicationDate": {
    "$date": "2023-07-28T00:00:00Z"
  },
  "type": "pbook",
}
9

Are you sure you are looking at the same document?

I am not.

The one in Atlas has an appropriate date that ends with .0000+00:00 while the other one ends with Z.

It looks like the document 64e73f03ed4d31d42c8d7b58 really has a field named publicationDate which is an object that has a field named $date with a string value.

That would explain that you get:

Try the queries

filter = {"publicationDate.$date": {"$type": "string"}}
print(collection.count_documents(filter))

and

filter = {"publicationDate.$date" : "2023-07-28T00:00:00Z" }
print(collection.count_documents(filter))