mongodb indexing subarray values

Question

if i have in mongodb a array structure like this:

"field":
[
     [10,20]  //specified length per array
     [25,40]
     [60,90]
     ...and so on, with various size per document
]

how can i index this subarrays? i tried to index with: db.ensureIndex({"field.$.0":1}); //and {"field.$.1":1}

but searching in documents is remained slow.

I tried also this solution: MongoDB, array of arrays index but

db.ensureIndex("{field:[{"a":1}]}"); //and "{field:[{"b":1}]}"

if a naming the subarray indexes a and b, this sintax throws "bad index key pattern" exception.

What are your query requirements?

Derick
– Derick

2013-08-16 13:58:40 +00:00
Commented Aug 16, 2013 at 13:58 — Derick
– Derick, Commented Aug 16, 2013 at 13:58

Mason · Accepted Answer · 2013-08-16 13:55:03Z

4

Can you explain more what you're trying to do? The first schema design is not very good; you have a bunch of arrays that you really have no way to address except for using the array operators which can be very slow.

It seems like you were on track with your second idea however your syntax is just a little off. If you can insert documents instead of sub-arrays you'll find the schema much easier to deal with and you can index on the two values in each document as illustrated below:

> db.test.insert({field: [{a:1, b:2}, {a:3, b:4}]})
> db.test.ensureIndex({"field.a":1})
> db.test.ensureIndex({"field.b":1})
> db.test.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "ns" : "test.test",
                "name" : "_id_"
        },
        {
                "v" : 1,
                "key" : {
                        "field.a" : 1
                },
                "ns" : "test.test",
                "name" : "field.a_1"
        },
        {
                "v" : 1,
                "key" : {
                        "field.b" : 1
                },
                "ns" : "test.test",
                "name" : "field.b_1"
        }
]
> db.test.find({"field.a": 3})
{ "_id" : ObjectId("520e2ec749177daf439a2ff6"), "field" : [ { "a" : 1, "b" : 2 }, { "a" : 3, "b" : 4 } ] }

You can run an explain to see that, indeed, the index is being used (see the cursor line)

> db.test.find({"field.a": 3}).explain()
{
        "cursor" : "BtreeCursor field.a_1",
        "isMultiKey" : true,
        "n" : 1,
        "nscannedObjects" : 1,
        "nscanned" : 1,
        "nscannedObjectsAllPlans" : 1,
        "nscannedAllPlans" : 1,
        "scanAndOrder" : false,
        "indexOnly" : false,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "millis" : 0,
        "indexBounds" : {
                "field.a" : [
                        [
                                3,
                                3
                        ]
                ]
        },
        "server" : "xxxxx-PC:27017"
}

answered Aug 16, 2013 at 13:55

Mason

8,85610 gold badges36 silver badges34 bronze badges

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

5 Comments

Dankó Dávid Over a year ago

So "i try to find a document wich is in range of:..." but there is multiple ranges, 10 to 20, 25 to 40 and so on, and i don't know ho many range will be inserted, so i (think) need to use a field wich is a array that contains some 2 element array, thats contains the minimal and maximal values, and this i need to fast lookup.(and i'm new in noSQL)

Mason Over a year ago

If I understand your question instead of doing db.test.find({"field.a" : 3}) you can do db.test.find({field : {$elemMatch: { a: {$lte: 3}, b: {$gte: 3} } } }). That will find you the document where the field has a range that contains the value 3. If you run an explain you'll see that the index is used as expected.

Dankó Dávid Over a year ago

yes i want find that (and i also can find it) but too slow, and i dont know how can i indexing that fields. Somewhere i seen: field.$.0 field.$.1 is the solution but it is not works.

Mason Over a year ago

Look at the code in my comment above and try it - db.test.find({field : {$elemMatch: { a: {$lte: 3}, b: {$gte: 3} } } }). Set your index as db.ensureIndex({"field.a":1}). If you run an explain you'll see that it uses indexes. If it's still too slow then you need to reexamine your schema because you're not going to get any faster than an indexed query without changing something.

Dankó Dávid Over a year ago

this solution is too slow, i really need modify schema, thanks for help

Collectives™ on Stack Overflow

mongodb indexing subarray values

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related