CODEWITHSUNDEEP
pythonregexmongodbpymongodatabase
user2535621
user2535621

Reputation: 157

mongodb, pymongo - sort matching data by another column

I have a collection in this format:

Location {
    'id' : ObjectID,
    'location' : string,
    'country' : string
}

I want to match the documents that contain a certain substring in the 'location' field, I only need 20 matches and I want the locations with a 'country' field that is equal to USA to come first (what I mean by this is: if I have 15 USA locations that match my criteria, and 15 Canada locations that match my criteria, I want to include all 15 of the USA locations and 5 of the Canada locations in the result; if I have more than 20 USA locations that match my criteria, than I want to include only the USA locations in my result).

I know that this can be done easily by making 2 queries (first search the locations in USA, and then in the rest of the world), or by one query that retrieves all the documents that match my criteria, and then filtering int it for USA locations, but I think that this is not the fastest solution.

For now, I'm just matching documents by my criteria, ignoring the country, using this command in pymongo:

reg_ex = '(?i).*' + substringCriteria + '.*'
result = db['Location'].find({ 'location' : { '$regex' : reg_ex } }).limit(20)

I think that the 'limit' part makes my query faster (I think MongoDB just stops searching for more matches when it reaches for 20), so if this is true, is there any way I can use this to solve my aforementioned problem?

Upvotes: 0

Views: 102

Answers (1)

Malcolm Murdoch
Malcolm Murdoch

Reputation: 1085

Assuming your case is consistent:

db.location.find({'location':{$regex:'blah'}}).sort({'country':-1}).limit(20)

Should work fine assuming it's only USA and Canada

Upvotes: 1

Related Questions