How can I sort mongodb regex search query results based on regex match count

Question

I can't figure out how to sort query results based on the "best" match.

Here's a simple example, I have a "zone" collection containing a list of city/zipcode couples.

If I search several words through the regex using the "and" keyword like that :

"db.zones.find({$or : [ {ville: /ROQUE/}, {ville: /ANTHERON/}] })"

Results won't be ordered by "best match".

What other solutions do I have for that ?

There isn't a way to order by this definition of best match. The meaning of the regex in find is binary - a document does or does not match. Your best options are to use text search as suggested below, or, if you return a small number of documents, manually re-sort the docs into whatever best match order you have in mind. — wdberkeley
– wdberkeley, Commented Dec 1, 2014 at 16:10
Even using $text search doesn't seem to be helpful in keywords match ranking... what solutions do I have except sorting array itself through code ? — David Fabreguette
– David Fabreguette, Commented Dec 2, 2014 at 8:06
$text search does rank by strength of match, it just may not be what you need. If you need a sort order that's not by fields or by text match score, you'll need to do it yourself. If you limit the returned results to 10-50, I don't think the extra time to sort would be noticeable for, say, a web application. — wdberkeley
– wdberkeley, Commented Dec 3, 2014 at 15:40
Anyway, I think it's a pretty common thing to sort by key words match, isn't it ? — David Fabreguette
– David Fabreguette, Commented Dec 9, 2014 at 13:54

Barno · Accepted Answer · 2014-12-01 08:46:14Z

1

You could try to use http://docs.mongodb.org/manual/reference/operator/query/text/#match-any-of-the-search-terms

db.zones.ensureIndex( {  'ville' : 'text' } ,{ score: {$meta:'textScore'}}) 

db.zones.find(
   { $text: { $search: "ROQUE ANTHERON"}},
   { score: { $meta: "textScore" } }
).sort( { score: { $meta: "textScore" } } )

Result:

{
    "_id" : ObjectId("547c2473371ea419f07b954c"),
    "ville" : "ANTHERON",
    "score" : 1.1
}

{
    "_id" : ObjectId("547c246f371ea419f07b954b"),
    "ville" : "ROQUE",
    "score" : 1
}

From documentation

If the search string is a space-delimited string, $text operator performs a logical OR search on each term and returns documents that contains any of the terms.

You have to use mongodb 2.6

answered Dec 1, 2014 at 8:46

Barno

3,3415 gold badges31 silver badges60 bronze badges

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

9 Comments

David Fabreguette Over a year ago

Looks like in your case the text "ROQUES" is returned before "LA ROQUE-D'ANTHERON".

David Fabreguette Over a year ago

May be it's a normal behavior - but then my question will be how can I make sure in this case, "LA ROQUE-D'ANTHERON" is returned before "ROQUES"...

Barno Over a year ago

@user3559701 I don't know if is the way, but if you search

db.zones.find(    { $text: { $search: "LA ROQUE-D'ANTHERON"}},    { score: { $meta: "textScore" } } ).sort( { score: { $meta: "textScore" } } )

The score is 2 and you find it first Roques.. Let me know :)

David Fabreguette Over a year ago

That's right but logically, if I search "ROQUE" and "ANTHERON" I should get first "LA ROQUE-D'ANTHERON" ? (highlier scored than the others) but thank you for your proposal :)

Barno Over a year ago

if you search

db.zones.find( { $text: { $search: "LA ROQUE-D'ANTHERON"}}, { score: { $meta: "textScore" } } ).sort( { score: { $meta: "textScore" } } )

, you find first "LA ROQUE-D'ANTHERON" with score 2 and ROQUES with score 1 pastebin.com/8yC1FgRf, and it's right

|

David Fabreguette · Accepted Answer · 2015-10-09 14:20:49Z

1

I ended up using ElasticSearch search engine do this query :

@zones = Zone.es.search(
    body: {
      query: {
        bool: {
          should: [
            {match: {city: search}},
            {match: {zipcode: search.to_i}}
          ]
        }
      },
      size: limit
    })

Where search is a search param sent by view.

ElasticSearch with Selectize plugin

answered Oct 9, 2015 at 14:20

David Fabreguette

1172 silver badges7 bronze badges

Collectives™ on Stack Overflow

How can I sort mongodb regex search query results based on regex match count

2 Answers 2

9 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related