[CSHARP-1899] Indexs on fields in an array of subdocuments Created: 20/Jan/17  Updated: 31/Mar/22

Status: Backlog
Project: C# Driver
Component/s: Builders
Affects Version/s: None
Fix Version/s: None

Type: New Feature Priority: Minor - P4
Reporter: John Fricker Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: triaged
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   

I have this query:

	var cursor = groupCollection
					.Find(
						teamsQueryBuilder.ElemMatch(g => g.Teams, teamQueryBuilder.Eq(t => t.TeamId, teamId))
					);
 
	cursor.Options.Modifiers = new BsonDocument("$explain", true);
	var result = cursor.Project(new BsonDocument()).FirstOrDefault();

So, I created an index like this:

            groupCollection.Indexes
                .CreateOne(Builders<Group>.IndexKeys
                    .Ascending(g => g.Teams[-1].TeamId));

        {
                "v" : 1,
                "name" : "teams.$.teamId_1",
                "key" : {
                        "teams.$.teamId" : 1
                },
                "ns" : "spochdb_dev.organization.teamgroups"
        }

When I ran the query, I got the following explain:

{
	"cursor" : "BasicCursor",
	"isMultiKey" : false,
	"n" : 1,
	"nscannedObjects" : 1,
	"nscanned" : 1,
	"nscannedObjectsAllPlans" : 1,
	"nscannedAllPlans" : 1,
	"scanAndOrder" : false,
	"indexOnly" : false,
	"nYields" : 0,
	"nChunkSkips" : 0,
	"millis" : 0,
	"indexBounds" : {},
	"allPlans" : [{
			"cursor" : "BasicCursor",
			"n" : 1,
			"nscannedObjects" : 1,
			"nscanned" : 1,
			"indexBounds" : {}
		}
	],
	"server" : "DESKTOP-J8M6V6N:27017"
}

But, if I change the index creation code to this:

	groupCollection.Indexes
		.CreateOne(Builders<Group>.IndexKeys
			.Ascending("teams.teamId"));

        {
                "v" : 1,
                "name" : "teams.teamId_1",
                "key" : {
                        "teams.teamId" : 1
                },
                "ns" : "spochdb_dev.organization.teamgroups"
        }

and run the query again, I get the following explain:

{ {
		"cursor" : "BtreeCursor teams.teamId_1",
		"isMultiKey" : true,
		"n" : 1,
		"nscannedObjects" : 1,
		"nscanned" : 1,
		"nscannedObjectsAllPlans" : 1,
		"nscannedAllPlans" : 1,
		"scanAndOrder" : false,
		"indexOnly" : false,
		"nYields" : 0,
		"nChunkSkips" : 0,
		"millis" : 0,
		"indexBounds" : {
			"teams.teamId" : [[ObjectId("5881760396839d0e94fdf5a5"), ObjectId("5881760396839d0e94fdf5a5")]]
		},
		"allPlans" : [{
				"cursor" : "BtreeCursor teams.teamId_1",
				"n" : 1,
				"nscannedObjects" : 1,
				"nscanned" : 1,
				"indexBounds" : {
					"teams.teamId" : [[ObjectId("5881760396839d0e94fdf5a5"), ObjectId("5881760396839d0e94fdf5a5")]]
				}
			}
		],
		"oldPlan" : {
			"cursor" : "BtreeCursor teams.teamId_1",
			"indexBounds" : {
				"teams.teamId" : [[ObjectId("5881760396839d0e94fdf5a5"), ObjectId("5881760396839d0e94fdf5a5")]]
			}
		},
		"server" : "DESKTOP-J8M6V6N:27017"
	}
}

We're using databse version v2.4.14 (git version 05bebf9ab15511a71bfbded684bb226014c0a553) at the moment, if that makes a difference.

If it doesn't make a difference, why is the driver generating unusable indexes?



 Comments   
Comment by James Kovacs [ 04/Feb/22 ]

In MongoDB, indexes on arrays of subdocuments are specified as teams.teamId. Specifying the index definition using a string works as desired.

	groupCollection.Indexes
		.CreateOne(Builders<Group>.IndexKeys
			.Ascending("teams.teamId"));

When you try to express this with a lambda, you cannot use g => g.Teams.TeamId because TeamId exists on the items of the collection, not on the collection itself. We do not currently support the proposed syntax g => g.Teams[-1].TeamId, though we shouldn't generate invalid index syntax.

Generated at Wed Feb 07 21:40:59 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.