Menu
In MongoDB, if I were to store an array (say ["red", "blue"]) in a field "color", does it index "red" and "blue" so I could query for "red", for example, or does in make {"red", "blue"} a composite index?
385
MongoDB uses multikey indexes to index the content stored in arrays. If you index a field that holds an array value, MongoDB creates separate index entries for every element of the array. These multikey indexes allow queries to select documents that contain arrays by matching on element or elements of the arrays.
Starting from MongoDB version 3.4 we can use the $indexOfArray operator to return the index at which a given element can be found in the array. $indexOfArray takes three arguments. The first is the name of the array field prefixed with $ sign.
Every document is indexed on every field, even nested fields inside arrays or objects. Rockset indexes every field automatically so users don't need to build indexes to make queries fast — queries are indexed by default. There is no limit to the number of fields which can be ingested and indexed.
When it comes to indexing arrays, MongoDB indexes each value of the array so you can query for individual items, such as "red." For example:
> db.col1.save({'colors': ['red','blue']}) > db.col1.ensureIndex({'colors':1}) > db.col1.find({'colors': 'red'}) { "_id" : ObjectId("4ccc78f97cf9bdc2a2e54ee9"), "colors" : [ "red", "blue" ] } > db.col1.find({'colors': 'blue'}) { "_id" : ObjectId("4ccc78f97cf9bdc2a2e54ee9"), "colors" : [ "red", "blue" ] } For more information, check out MongoDB's documentation on Multikeys: http://www.mongodb.org/display/DOCS/Multikeys
157
You can simply test index usage by appending "explain" to your query:
> db.col1.save({'colors': ['red','blue']}) # without index > db.col1.find({'colors': 'red'}).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "protrain.col1", "indexFilterSet" : false, "parsedQuery" : { "colors" : { "$eq" : "red" } }, "winningPlan" : { "stage" : "COLLSCAN", <--- simple column scan "filter" : { "colors" : { "$eq" : "red" } }, "direction" : "forward" }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "bee34f15fe28", "port" : 27017, "version" : "3.4.4", "gitVersion" : "888390515874a9debd1b6c5d36559ca86b44babd" }, "ok" : 1 } # query with index > db.col1.createIndex( { "colors":1 } ) > db.col1.find({'colors': 'red'}).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "protrain.col1", "indexFilterSet" : false, "parsedQuery" : { "colors" : { "$eq" : "red" } }, "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", <!---- INDEX HAS BEEN USED "keyPattern" : { "colors" : 1 }, "indexName" : "colors_1", "isMultiKey" : true, "multiKeyPaths" : { "colors" : [ "colors" ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "colors" : [ "[\"red\", \"red\"]" ] } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "bee34f15fe28", "port" : 27017, "version" : "3.4.4", "gitVersion" : "888390515874a9debd1b6c5d36559ca86b44babd" }, "ok" : 1 } For structures where you have structured indexes, you could use the array position to index fields inside arrays:
{ '_id': 'BB167E2D61909E848EBC96C7B33251AC', 'hist': { 'map': { '10': 1 } }, 'wayPoints': [{ 'bhf_name': 'Zinsgutstr.(Berlin)', 'ext_no': 900180542, 'lat': 52.435158, 'lon': 13.559086, 'puic': 86, 'time': { 'dateTime': '2018-01-10T09: 38: 00', 'offset': { 'totalSeconds': 3600 } }, 'train_name': 'Bus162' }, { 'bhf_name': 'SAdlershof(Berlin)', 'ext_no': 900193002, 'lat': 52.435104, 'lon': 13.54055, 'puic': 86, 'time': { 'dateTime': '2018-01-10T09: 44: 00', 'offset': { 'totalSeconds': 3600 } }, 'train_name': 'Bus162' }] } db.col.createIndex( { "wayPoints.0.ext_no":1 } ) db.col.createIndex( { "wayPoints.0.train_name":1 } ) db.col.createIndex( { "wayPoints.1.ext_no":1 } ) db.col.createIndex( { "wayPoints.1.train_name":1 } ) > db.col.find( ... { ... "wayPoints.ext_no": 900180542 ... } ... , ... { ... "wayPoints.ext_no":1, ... "wayPoints.train_name":1, ... "wayPoints.time":1 ... } ... ).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "db.col", "indexFilterSet" : false, "parsedQuery" : { "wayPoints.ext_no" : { "$eq" : 900180542 } }, "winningPlan" : { "stage" : "PROJECTION", "transformBy" : { "wayPoints.ext_no" : 1, "wayPoints.train_name" : 1, "wayPoints.time" : 1 }, "inputStage" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "wayPoints.ext_no" : 1 }, "indexName" : "wayPoints.ext_no_1", "isMultiKey" : true, "multiKeyPaths" : { "wayPoints.ext_no" : [ "wayPoints" ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "wayPoints.ext_no" : [ "[900180542.0, 900180542.0]" ] } } } }, "rejectedPlans" : [ ] }, "serverInfo" : { "host" : "bee34f15fe28", "port" : 27017, "version" : "3.4.4", "gitVersion" : "888390515874a9debd1b6c5d36559ca86b44babd" }, "ok" : 1 } If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
