MongoDB Error: <timeField> must be present and contain a valid BSON UTC datetime value

I am trying to import what has now become an incredibly simple JSON to MongoDB using the Import Data button in MongoDB Compass.

I started with this:

{"timestamp":"1728714859000","prio":"0"}
{"timestamp":"1728714859000","prio":"0"}

Which resulted in the import error:

 {"name":"WriteError","message":"'timestamp' must be present and contain a valid BSON UTC datetime value","index":1,"code":2,"op: {"timestamp":"1728714859000","prio":"0","_id":"67397afef3e6b1d4dc9c2f44"}}

Through trial and error, I discovered that removing the quotes from timestamp, as follows, gets me past that error.

{timestamp:"1728714859000","prio":"0"}
{timestamp:"1728714859000","prio":"0"}

Now I am presented with a new error, but only in the GUI, which states:

Failed to import with the following error:

Parser cannot parse input: expected an object key

After looking around at some other examples, I found a reference to a _id and another way to do the timestamp.

{
    "_id" : "5d11c815eb946a412ecd677d",
    "timestamp" : ISODate("2024-10-10T05:06:44.871Z"),
    "name" : "harry"
}

Now I am getting the error:

Failed to import with the following error:

Parser cannot parse input: expected a value

Following another suggestion I tried to insert data using Python:

import pymongo
import time

client = pymongo.MongoClient("mongodb://localhost:27017/")
db = client["sklogsdb"]
collection = db["sklogscol"]

record = {
    "timestamp": int(time.time() * 1000),
    "email": "johndoe@example.com"
}

result = collection.insert_one(record)

This resulted in the same error:

pymongo.errors.WriteError: 'timestamp' must be present and contain a valid BSON UTC datetime value, full error: {'index': 0, 'code': 2, 'errmsg': "'timestamp' must be present and contain a valid BSON UTC datetime value"}

What am I doing wrong here?

Solution

That error indicates the data is being loaded into a timeseries collection, which does require a BSON UTC datetime in the defined time field.

The BSON spec (https://bsonspec.org/spec.html) defines UTC datetime as:

    signed_byte(9) e_name int64     UTC datetime

where the int64 is the number of milliseconds since epoch.

In many query forms, MongoDB permits using an integer to query against a datetime, matching just the int64. However, when storing data, the type of the input is maintained.

As you may know, JSON does not have a datatype to represent a date/time, so MongoDB has defined an extended JSON format to carry additional type data to permit import/export of documents while retaining datatypes not supported by JSON.

Specific to this instance, the MongoDB extended JSON representation of a UTC Datetime is:

Canonical
{"$date": {"$numberLong": "<millis>"}}

Relaxed 
{"$date": "<ISO-8601 Date/Time Format>"}

MongoDB Atlas and Compass are able to import/export extended JSON, so you should be able to use that format.