MongoDB Index on field with multiple identifications as nested objects

Question

I have a mongoDB collection containing items that can be identified through multiple identification schemes

{ 
    "identification" : {
       "SCHEME1" : [ "9181983" ], 
       "SCHEME2" : [ "ABC" , "CDE" ], 
       "SCHEME4" : ["FDE"]
    } 
}

{ 
    "identification" : {
       "SCHEME2" : [ "LALALAL" ], 
       "SCHEME5" : [ "CH98790789879" ] 
    } 
},

An item will most likely have not all identification schemes, some have (like the example above ) 1-2-4 others may have different ones. The number of identification schemes is not finally defined and will grow. Every identification can only exists once.

I want to perform two different queries:

Seach an item with scheme and identification, e.g.

 db.item.find({"identification.SCHEME2": "CDE"})

Seach all items with a specific identification scheme, e.g.

db.item.find({"identification.SCHEME2": {$exists: true}})

My approach was to create sparse indexes:

db.item.createIndex( { identification.SCHEME1: 1 }, { sparse: true, unique: true} );
db.item.createIndex( { identification.SCHEME2: 1 }, { sparse: true, unique: true } );
db.item.createIndex( { identification.SCHEME3: 1 }, { sparse: true, unique: true } );
and so on ....

This approach worked perfectly until I found out that there is a limit of 64 indexes on one collection in mongoDB.

Has anyone an idea how I could index the whole field "identification" with one index ? Or is my document structure wrong ? Any ideas are welcome, thanks.

RickyA · Accepted Answer · 2015-10-15 13:39:50Z

1

I encountered the same problem in a reporting db that had dimensions that I wanted to use in the find clause. The solution was to use a fixed field to hold the data as a k/v pair and index on that. In your case:

{ 
    "identification" : [
       {"k":"SCHEME1", "v":[ "9181983" ]}, 
       {"k":"SCHEME2", "v":[ "ABC" , "CDE" ]}, 
       {"k":"SCHEME4", "v":["FDE"]}
    ] 
}

If you now create a compound index over {"identification.k":1, "identification.v":1} you can search it with the index like:

db.item.find({"identification.k":"SCHEME2", "identification.v":"CDE"})

Downside is you need to update your schema...

answered Oct 15, 2015 at 13:39

RickyA

16.1k6 gold badges77 silver badges97 bronze badges

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

2 Comments

mmx73 Over a year ago

Does a unique index still works on that to prevent duplicate identifications , e.g. identification.SCHEME2 : ABC ? How ever, the rebuild my collection will not be that fun :-)

mmx73 Over a year ago

to answer my own question form the comment above: yes, a unique key works perfect. the change of my schema I could do with a small batch and an enhancement in my java object model. thanks again.

Collectives™ on Stack Overflow

MongoDB Index on field with multiple identifications as nested objects

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related