How can I sort by timestamp mongodb documents, using atlas search index?

MongoDB Atlas Atlas Search
,
1

I have multiple documents that have dates and timestamps really close relative to each other. Here is the data I have:

/* 1 */
{
    "startDate" : ISODate("2022-11-29T14:28:38.166Z"),
    "startTimestamp" : 1669732118166,
    "customerName" : "Eduardo Bechtelar"
}

/* 2 */
{
    "startDate" : ISODate("2022-11-29T14:28:38.258Z"),
    "startTimestamp" : 1669732118258,
    "customerName" : "Sylvia Wolf"
}

/* 3 */
{
    "startDate" : ISODate("2022-11-29T14:28:38.284Z"),
    "startTimestamp" : 1669732118284,
    "customerName" : "Jeremy McLaughlin"
}

/* 4 */
{
    "startDate" : ISODate("2022-11-29T14:28:38.298Z"),
    "startTimestamp" : 1669732118298,
    "customerName" : "Laura Lynch"
}

/* 5 */
{
    "startDate" : ISODate("2022-11-29T14:28:38.311Z"),
    "startTimestamp" : 1669732118311,
    "customerName" : "Noel Lubowitz"
}

/* 6 */
{
    "startDate" : ISODate("2022-11-29T14:28:38.435Z"),
    "startTimestamp" : 1669732118435,
    "customerName" : "Natalie Cummerata DVM"
}

Here is the index definition:

{
  "mappings": {
    "dynamic": false,
    "fields": {
      "customerName": {
        "type": "autocomplete"
      },
      "startDate": {
        "type": "date"
      },
      "startTimestamp": {
        "representation": "int64",
        "type": "number"
      }
    }
  }
}

As you can see the timestamps are really closed to each other. The difference is in milliseconds. What I’m trying to do is to sort the items asc or desc based on start timestamp using near operator. What I saw is that search index cannot handle big numbers using timestamps.

I tried to sort items descending using the near operator, like this:

var now = Date.now()

db.getCollection('search-tests').aggregate([
    {
        $search: {
            index: "testingSearchIndex",
            compound: {
                should: [
                       {
                           near: {
                               path: "startTimestamp",
                               origin: now,
                               pivot: 1,
                           }
                       },
                ]
            }
        }
    },
    {
        $project: {
            startDate: 1,
            startTimestamp: 1,
            score: { $meta: "searchScore" },
        }
    }
])

I was expecting to see items sorted descending based on startTimestamp. But the sorting is kind of random. What am I doing wrong?

3

Hi @Andrei_Batinas,

I believe what you’re seeing is because of the score being the same assuming the variable now is relatively large amount away from the startTimestamp values for your sample documents - Since you projected the score field I presume the score for all those documents were the same?

Will send a DM with some more information that may help.

Regards,
Jason