How to create MongoDB MultiKey index on attribute of items in an array .NET Driver

Question

I have a MongoDB collection "foos" containing items which each have an array of "bars". That is, "foo" has the following schema:

{
    "id": UUID
    "name": string
    ...
    "bars": [
        "id": UUID
        "key": string
        ...
    ]
}

I need to create an index on name and bar.key using the MongoDB C# .NET Mongo driver.

I presumed I could use a Linq Select function to do this as follows:

Indexes.Add(Context.Collection<FooDocument>().Indexes.CreateOne(
    Builders<FooDocument>.IndexKeys
        .Descending(x => x.Bars.Select(y => y.Key))));

However this results in an InvalidOperationException:

System.InvalidOperationException: 'Unable to determine the serialization information for x => x.Bars.Select(y => y.Id).'

The Mongo documentation on MultiKey indexes shows how to create such an index using simple dot notation, i.e.

db.foos.createIndex( { "name": 1, "bars.key": 1 } )

However the MongoDB driver documentation seems to suggest that using a Linq function as I'm doing is correct.

How can I create a multikey index on my collection using the MongoDB .NET Driver, preferably using a Linq function?

I know this will work var indexDefinition = Builders<FooDocument>.IndexKeys.Descending("Bars.Key");. I don't know if there is a linq function to support projecting embedded doc/arrays. — s7vr
– s7vr, Commented May 19, 2017 at 13:45
I went with this solution for now, it's a shame to have to hardcode the magic string - perhaps a future feature of the driver and I can update this. — 08Dc91wk
– 08Dc91wk, Commented May 22, 2017 at 10:33

Artyom · Accepted Answer · 2017-05-19 15:47:09Z

20

This is an example how to do it with C#

var indexDefinition = Builders<FooDocument>.IndexKeys.Combine(
    Builders<FooDocument>.IndexKeys.Ascending(f => f.Key1),
    Builders<FooDocument>.IndexKeys.Ascending(f => f.Key2));

await collection.Indexes.CreateOneAsync(indexDefinition);

UPDATE

Regarding index within the array, closest what i was able to find is to use "-1" as index whene you building your index key. As i understand from github source code is is a valid option in case of building queries.

var indexDefinition = Builders<FooDocument>.IndexKeys.Combine(
    Builders<FooDocument>.IndexKeys.Ascending(f => f.Key1),
    Builders<FooDocument>.IndexKeys.Ascending(f => f.Key2[-1].Key));

await collection.Indexes.CreateOneAsync(indexDefinition);

"-1" is a hardcoded constant in side mongodb C# drivers which means "$" (proof). So this code would try to create index:

{ "Key1": 1, "Key2.$.Key": 1 }

which is fine for querying info from database, but not allowed (will throw an exception "Index key contains an illegal field name: field name starts with '$'") to use in indexes. So i assume it should be changed in mongodb drivers to make it work. Something like "-2" means empty operator. In that case we could use

var indexDefinition = Builders<FooDocument>.IndexKeys.Combine(
    Builders<FooDocument>.IndexKeys.Ascending(f => f.Key1),
    Builders<FooDocument>.IndexKeys.Ascending(f => f.Key2[-2].Key));

await collection.Indexes.CreateOneAsync(indexDefinition);

which would generate index like:

{ "Key1": 1, "Key2.Key": 1 }

So basically i don't think it is possible right now to build index you want with pure Linq without changing mongo C# drivers.

So i think your only option do like this, still C# but without Linq

await collection.Indexes.CreateOneAsync(new BsonDocument {{"name", 1}, {"bars.key", 1}});

edited May 19, 2017 at 15:47

answered May 19, 2017 at 12:57

Artyom

1,0959 silver badges13 bronze badges

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

4 Comments

08Dc91wk Over a year ago

Thanks for your answer. But Key2 is a property of the items within an array, so it's f.Key2.Select(b => b.Key3). That's not working, which is the problem I'm having.

Artyom Over a year ago

Hmm, my bad, i've got your point now. I was thinking issue is with lack of ".Combine()"

08Dc91wk Over a year ago

+1 for your effort in looking for a solution - I don't think an array index is any better than a magic string though, which is what I went with. I am marking this as the answer: "I don't think it is possible right now" as you say.

Ostati Over a year ago

Kudos to MongoDB devs for making this an idempotent op!

Noldorin · Accepted Answer · 2017-09-26 00:01:15Z

9

This appears to be a requested feature for the C# driver, although it hasn't seen any progress lately. That said, someone did submit a rough-and-ready solution there on the JIRA thread, so perhaps that will do the job for you.

answered Sep 26, 2017 at 0:01

Noldorin

148k56 gold badges273 silver badges308 bronze badges

1 Comment

Michika Iranga Perera Over a year ago

use this

var indexKeysDefinition = Builders<User>.IndexKeys     .Ascending("Logins.LoginProvider")     .Ascending("Logins.ProviderKey");

for more info go to this JIRA

Electric Sheep · Accepted Answer · 2017-11-30 13:50:44Z

6

You can create a string index and use nameof() in C# 6:

Indexes.Add(Context.Collection<FooDocument>().Indexes.CreateOne($"{nameof(FooDocument.Bars)}.{nameof(Bars.Key)}"));

edited Nov 30, 2017 at 13:50

answered Nov 23, 2017 at 18:06

Electric Sheep

4,4201 gold badge31 silver badges40 bronze badges

Comments

nandithakw · Accepted Answer · 2019-04-07 08:34:58Z

2

As of "MongoDB.Driver" Version="2.8.0" syntax has been changed and some of the methods has been deprecated.Following is the way to achieve the same . Please note the CreateOneAsync(new CreateIndexModel<FooDocument>(indexDefinition)); part

var indexDefinition = Builders<FooDocument>.IndexKeys.Combine(
    Builders<FooDocument>.IndexKeys.Ascending(f => f.Key1),
    Builders<FooDocument>.IndexKeys.Ascending(f => f.Key2[-1].Key));


  await collection.Indexes.CreateOneAsync(new CreateIndexModel<FooDocument>(indexDefinition));

answered Apr 7, 2019 at 8:34

nandithakw

1,04912 silver badges16 bronze badges

Comments

Adriaan de Beer · Accepted Answer · 2020-09-07 05:38:15Z

1

As per a previous comment, C# driver still doesn't support a strongly typed way of doing multikey indexes.

Also, using something like [-1] seems bit hacky and not really what you're after as it'll substitute it with $.

As such I suggest doing this (as per MongoDB.Driver 2.8.0 onwards):

var indexDefinition = Builders<FooDocument>.IndexKeys.Combine(
    Builders<FooDocument>.IndexKeys.Ascending(f => f.Key1),
    Builders<FooDocument>.IndexKeys.Ascending($"{nameof(FooDocument.Key2)}.{nameof(BarType.Key)}"));

await collection.Indexes.CreateOneAsync(new CreateIndexModel<FooDocument>(indexDefinition), cancellationToken: token);

answered Sep 7, 2020 at 5:38

Adriaan de Beer

1,28616 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to create MongoDB MultiKey index on attribute of items in an array .NET Driver

5 Answers 5

4 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related