Hi @Helene_ORTIZ
@steevej is absolutely correct that the projection stage prevents MongoDB from using the index. Since an index points to the physical location of a document, I believe once you have a stage that loses this physical connection (e.g. projection, grouping, etc.), indexes generally cannot be used anymore.
So I tried to create some example documents:
{"_id": 1,date: new Date("2021-01-01"), "lineNumber": 1, "id": "x", "cycle": "y", "type": "z"}
{"_id": 2,date: new Date("2021-01-02"), "lineNumber": 2, "id": "x", "cycle": "y", "type": "z"}
{"_id": 3,date: new Date("2021-01-03"), "lineNumber": 3, "id": "x", "cycle": "y", "type": "z"}
{"_id": 4,date: new Date("2022-01-04"), "lineNumber": 4, "id": "x", "cycle": "y", "type": "z"}
{"_id": 5,date: new Date("2022-01-05"), "lineNumber": 5, "id": "x", "cycle": "y", "type": "z"}
Then I tried implementing your aggregation (I think this should be similar to your code):
var agg = Arrays.asList(
Aggregates.match(
Filters.and(
Filters.gte("date", LocalDate.of(2021, 1, 1)),
Filters.lt("date", LocalDate.of(2022, 1, 1)))),
Aggregates.sort(
Sorts.orderBy(
Sorts.descending("date"), Sorts.ascending("lineNumber"))),
Aggregates.project(
Projections.fields(
Projections.include("id", "cycle", "lineNumber", "date")))
);
coll.aggregate(agg).forEach(doc -> System.out.println(doc.toJson()));
and I found that the index was used in this case.
To ensure that the index is really used, I used the explain output code taken from the java driver documentation:
var explainResult = coll.aggregate(agg).explain();
System.out.println(explainResult.toJson(JsonWriterSettings.builder().indent(true).build()));
The output of explain is discussed in the explain results documentation page, but in a nutshell, if the index is not used, then you should see a COLLSCAN somewhere. If the index is used, you should see IXSCAN instead, without any COLLSCAN anywhere. In my test using the code above, I only see IXSCAN as expected.
If you’re still having issues, could you post the output of your explain output, and some example documents?
Best regards
Kevin