mongodb, pymongo - sort matching data by another column

Question

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?

Malcolm Murdoch · Accepted Answer · 2014-08-20 14:34:32Z

1

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

answered Aug 20, 2014 at 14:34

Malcolm Murdoch

1,0856 silver badges9 bronze badges

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

1 Comment

user2535621 Over a year ago

Thanks, it works. It's not only USA and Canada, but I added a 'priority' field and I set this field to 0 for USA and 1 for the other countries and I sorted by this field.

Collectives™ on Stack Overflow

mongodb, pymongo - sort matching data by another column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related