CODEWITHSUNDEEP
mongodbmongodb-query
RoyHB
RoyHB

Reputation: 1725

Mongo query existence and value of embedded object property

A 5,000 to 10,000 record Mongo collection contains:

{ 
    "_id" : ObjectId("55e16c34c78b04f43f2f55a0"), 
    "appID" : NumberInt(4830800), 
    "topics" : {
        "test1" : 1.440899998865E12, 
        "test2" : 1.440899998865E12, 
        "test3" : 1.440899998865E12, 
        "test4" : 1.440899998865E12
    }, 
}

I need to query for records that contain a specified property name in the topics field and where the value of the specified name is greater than or equal to a given number.

something like 

find({"topics.test1": { $gte: 1440825382535 }})

This query works as expected, returning a set of records that have a test1 property with a test1 value >= 1440825382535

If I create a simple index on the topics field explain() says that no index is used for the query (understandably).

The set of property names that may be searched for is not predefined. The query is dynamically built based on names that are found elsewhere.

Is there a way to index this table to speed up queries? The full scan query takes quite a bit of time to run (on the order of 1.5 seconds).

Upvotes: 3

Views: 818

Answers (2)

JohnnyHK
JohnnyHK

Reputation: 311835

To make this type of data indexable, you need to change the schema to make topics an array and move the dynamic test1, test2, etc. keys into values.

So something like:

{ 
    "_id" : ObjectId("55e16c34c78b04f43f2f55a0"), 
    "appID" : NumberInt(4830800), 
    "topics" : [
        {name: "test1", value: 1.440899998865E12}, 
        {name: "test2", value: 1.440899998865E12}, 
        {name: "test3", value: 1.440899998865E12}, 
        {name: "test4", value: 1.440899998865E12}
    ] 
}

Then your query changes to:

find({topics: {$elemMatch: {name: 'test1', value: {$gte: 1440825382535}}}})

Which you can support with an index of:

{'topics.name': 1, 'topics.value': 1}

Upvotes: 2

user3200120
user3200120

Reputation:

Was a little confused at what you were trying to do, but maybe something like this?

find({"topics.test1": {$exists: true}, { $gte: 1440825382535 }})

Upvotes: 1

Related Questions