0

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.

2
  • 1
    Can you give at least 2 documents in the example? It's hard to reproduce wrong sorting with a single document. Commented Sep 27, 2023 at 15:04
  • @AlexBlex, it is issue with index. Now we resolved the issue by rebuild the index Commented Oct 3, 2023 at 10:25

1 Answer 1

0

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

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.