Sorting not working in Search index - MongoDB Atlas

Question

We are currently using MongoDB Atlas as our database solution and have a Indexing and query optimization requirement. We have created a query that partially fulfills our needs, but the results are not meeting our expectations.

Our Requirements:

1. Search: We need to full text search in a collection.
2. Date Range: The query should allow us to specify a date range.
3. Sort by Time: Results should be sorted by timestamp in descending order.
4. Skip and Limit (Pagination): We require the ability to skip through results and limit the number of results per page.

Here's the current index and query we've been using for your reference:

Indexing created in Mongodb atlas

{
  "mappings": {
    "dynamic": false,
    "fields": {
      "level": {
        "type": "string"
      },
      "message": [
        {
          "dynamic": true,
          "type": "document"
        },
        {
          "type": "string"
        }
      ],
      "timestamp": {
        "type": "date"
      }
    }
  },
  "storedSource": {
    "include": [
      "timestamp",
      "level",
      "message"
    ]
  }
}

Query

   [
      {
        "$search": {
          "index": "srchIndxLogs***",
          "compound": {
            "must": [
              {
                "range": {
                  "path": "timestamp",
                  "gte": "2023-08-14T18:30:00.000Z",
                  "lte": "2023-09-15T18:29:59.000Z"
                }
              },
              {
                "phrase": {
                  "query": "TEST062",
                  "path": ["level", "message"]
                }
              }
            ]
          },
          "sort": {
            "timestamp": -1
          }
        }
      },
      {
        "$skip": 0
      },
      {
        "$limit": 100
      },
      {
        "$project": {
          "_id": 1,
          "timestamp": 1,
          "level": 1,
          "message": 1
        }
      }
    ]

Sample data

{"_id":{"$oid":"64f17d25a71e2f001cc19107"},"timestamp":{"$date":{"$numberLong":"1693547813848"}},"level":"info","message":"TEST062 device connected to the server","meta":null}

Sorting not working as expected in this query and search index, Please give the solution on this.

Answer 1

It is issue with Mongo db atlas index, Suggested to rebuild the index.

For preexisting indexes, you must rebuild the indexes to use the date and number fields in the indexes for sorting the Atlas Search results.

Reference : https://www.mongodb.com/docs/atlas/atlas-search/sort/#rebuild-index-for-sorting