Indexing multi dimentional array

Working with Data
,
1

Hi,
I have an array of dynamic arrays in the pathArrayMulti below.
I want to index this field. Is this possible?

 {
 	"_id" : "doc4",
 	"path" : "/f10/f4/",
 	"pathArray" : [ "f10", "f4" ],
 	"pathArrayMulti" : [
 		[ "f10", "f4" ],
 		[ "f1" ]
 	]
 }

If I create an index on pathArray like below, I can search the value and it will give me result back

db.doc.createIndex({“pathArray”: 1})
db.doc.find({pathArray: “f10”})

But if I create index on pathArrayMulti, and do the search, then empty resultset is returned.

db.doc.createIndex({“pathArrayMulti”: 1})
db.doc.find({pathArrayMulti: “f10”})

Is there any way for me to see the values that is stored in the index?

2

This should find the data, however the index will not be hit:

3

Hello there!

This query will also work:

db.testCol.find({ "pathArrayMulti.0": { $elemMatch: { $in: ["f10"] } } })

This query targets a more specific position within the array using dot notation ("pathArrayMulti.0" ). This allows MongoDB to directly access and match the first element of the pathArrayMulti array, without the need for multiple levels of $elemMatch. However, the index will not be used either.

Hope this helps :slight_smile:

4

Thanks both of you for the query syntax fixes :smiley:
I now get the data but my biggest issue still remain.
When this collection grows to million of documents it will be to slow without an index.

So is there any way to index or query this 2 dim array so the index is used?

5

Not that I’m aware of, you may need to re-factor the data to store it in an indexable layout :frowning:

Not hitting an index is a bit of deal breaker, as you say when data grows to reasonable levels you don’t want to have collection scans all over the palce.

6

You could also try using hint():

db.testCol.find({ "pathArrayMulti.0": { $elemMatch: { $in: ["f10"] } } }).hint({"pathArrayMulti" : 1})

This method overrides MongoDB’s default index selection and query optimization process. It forces MongoDB to use the specified index when performing the query.

7

The query

work ONLY because you are querying element 0 of pathArrayMulti. If the document was

{
 	"_id" : "doc4",
 	"path" : "/f10/f4/",
 	"pathArray" : [ "f10", "f4" ],
 	"pathArrayMulti" : [
 		[ "f1" ] ,
       	[ "f10", "f4" ]
 	]
 }

You would then to change the query to

db.testCol.find({ "pathArrayMulti.1": { $elemMatch: { $in: ["f10"] } } })

The solution shared by John_Sewell is the correct one.

8

You are right, @steevej!
I did mention it in my response:

This query targets a more specific position within the array using dot notation ("pathArrayMulti.0" ). This allows MongoDB to directly access and match the first element of the pathArrayMulti array

Maybe I should have been a bit clearer :slight_smile:

9

Sorry, I missed that part.

No. I should be a better reader.

10

I guess if you KNEW it would always be X dimensions you could $OR them together and then it would hit the index…

11

If you store the data as an array of objects, you can index them:

"pathArrayMulti" : [
       { values: [ "f1" ] },
       { values: [ "f10", "f4" ] }
]

Then you can create an index on { "pathArrayMulti.values": 1 }

12

I finally had the time to test that and it looks like it is working fine.

I was afraid that it would not used the index on queries like

{ "pathArrayMulti.values": "f10" }

because the indexed values would be the arrays.

Thanks