How to create a MongoDb index on key-name in Map[key, value]?

Question

We are currently managing a set of permissions as a Map on a collection. Each set of permissions is filed with the user's id as key. For some reason that key is currently a stringified version of a BSONObjectID.

I would like to maintain an index on the key, so I can lookup the relevant set of permissions only, and also find those documents where permissions for specific user is present.

edit: example added:
Example:

{"_id" : {"$oid" : "xxxxxx"},  
"irrelevantData" : "Document1 data...",  
"permissions" : {  
    "key1" : {"perm1" : true, "perm2: false},  
    "key3" : {"perm1" : true, "perm2: false}  
}  
{"_id" : {"$oid" : "yyyyyy"},  
"irrelevantData" : "Document2 data...",  
"permissions" : {  
    "key1" : {"perm1" : false, "perm2: true},  
    "key2" : {"perm1" : true, "perm2: false}  
}

In the example above, I'd like my index to be able to pick only documents where "key2" is present in permissions.

The model is like this:

case class relevantCollection(
  _id: BSONObjectID,
  irrelevantData: String,
  permissions: Option[Map[String, Map[String, Boolean]]]
)

How do I create an index on a key instead of a value? Is there any performance issue, regarding this key being a String vs. a BSONObjectID?

Before asking how to do it with ReactiveMongo, I would check the [MongoDB index doc](https://docs.mongodb.com/manual/indexes/) and try it the MongoShell. — cchantep, Jun 26 '16 at 11:04
@cchantep: I did check the [MongoDB index doc](https://docs.mongodb.com/manual/indexes/), but didn't find anything on how to index on a key. Maybe I'm reading it wrong? If I could just find a working solution for the MongoShell as you suggest, I would be happy :) — RastacraZ, Jun 26 '16 at 13:11
So if you don't find a way in the MongoDB documentation, there is no way to do it with any client library. — cchantep, Jun 26 '16 at 14:40
@cchantep: I don't know if I am reading you right neither, but I am translating your first comment as "try to solve your problem yourself", and the second as "if you can't find the answer yourself, there is no solution"? As much as you might be right, I still would like someone to reflect on the content of my question. — RastacraZ, Jun 29 '16 at 05:31

score 1 · Accepted Answer · answered Jun 29 '16 at 07:24

You can use sparse index for this requirement. The index will only have documents where "permissions.key2" is present.

db.permissions.createIndex({"permissions.key2" : 1}, {sparse: true })

The below query can be used to check whether the index is being used.

db.getCollection('permissions').find({"permissions.key2" : {
            "perm1" : true,
            "perm2" : false
        }}).explain();

In the output JSON, check the winning plan whether it has "IXSCAN".

"winningPlan" : {
    "stage" : "FETCH",
    "inputStage" : {
        "stage" : "IXSCAN",
        "keyPattern" : {
            "permissions.key2" : 1
        },
        "indexName" : "permissions.key2_1",
        "isMultiKey" : false,
        "isUnique" : false,
        "isSparse" : true,
        "isPartial" : false,
        "indexVersion" : 1,
        "direction" : "forward",
        "indexBounds" : {
            "permissions.key2" : [ 
                "[{ perm1: true, perm2: false }, { perm1: true, perm2: false }]"
            ]
        }
    }
}

Similarly, if you run the below query the winning plan would show "COLLSCAN".

db.getCollection('permissions').find({"permissions.key1" : {
            "perm1" : false,
            "perm2" : true
        }}).explain()

"winningPlan" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "permissions.key1" : {
                    "$eq" : {
                        "perm1" : false,
                        "perm2" : true
                    }
                }
            },
            "direction" : "forward"
        }

Thank you. This is what I have narrowed it down to myself. Unfortunately I don't know the key-names in advance, as they are dynamically added when a permission-object is added for each user_id (key* in example) holding one or more permissions. — RastacraZ, Jun 29 '16 at 09:27
Glad the issue is resolved. Would you mind to accept the answer for others benefit? — notionquest, Jun 29 '16 at 09:28
Marking this as the correct answer, because it is. Unfortunately what I want to do, doesn't seem to be possible: [http://stackoverflow.com/questions/9009987/improve-querying-fields-exist-in-mongodb](http://stackoverflow.com/questions/9009987/improve-querying-fields-exist-in-mongodb), [http://stackoverflow.com/questions/8176310/can-mongodb-use-an-index-when-checking-for-existence-of-a-field-with-exists-ope/14627290#14627290](http://stackoverflow.com/questions/8176310/can-mongodb-use-an-index-when-checking-for-existence-of-a-field-with-exists-ope/14627290#14627290). — RastacraZ, Jun 29 '16 at 09:30
Just curious, is there any way to index value-name in Map . For example - list : [ "1" : {"code" : 1, "a" : 2, "b": 3}] . Can i set unique index at list.code without knowing keys ? — Sagar Sahni, Mar 19 '20 at 05:37

How to create a MongoDb index on key-name in Map[key, value]?

1 Answers1