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 and try it the MongoShell. — cchantep
– cchantep, Commented Jun 26, 2016 at 11:04
@cchantep: I did check the MongoDB index doc, 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
– RastacraZ, Commented Jun 26, 2016 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
– cchantep, Commented Jun 26, 2016 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
– RastacraZ, Commented Jun 29, 2016 at 5:31

notionquest · Accepted Answer · 2016-06-29 07:24:17Z

1

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"
        }

answered Jun 29, 2016 at 7:24

notionquest

39.4k6 gold badges120 silver badges112 bronze badges

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

4 Comments

RastacraZ Over a year ago

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.

notionquest Over a year ago

Glad the issue is resolved. Would you mind to accept the answer for others benefit?

RastacraZ Over a year ago

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/8176310/can-mongodb-use-an-index-when-checking-for-existence-of-a-field-with-exists-ope/14627290#14627290.

Sagar Sahni Over a year ago

Just curious, is there any way to index value-name in Map<Key,Value> . For example - list : [ "1" : {"code" : 1, "a" : 2, "b": 3}] . Can i set unique index at list.code without knowing keys ?

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related