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:
- Search: We need to full text search in a collection.
- Date Range: The query should allow us to specify a date range.
- Sort by Time: Results should be sorted by timestamp in descending order.
- 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.