mongoDB prefix wildcard: fulltext-search ($text) find part with search-string

Question

I have mongodb with a $text-Index and elements like this:

{
   foo: "my super cool item"
}
{
   foo: "your not so cool item"
}

If i do search with

mycoll.find({ $text: { $search: "super"} })

i get the first item (correct).

But i also want to search with "uper" to get the fist item - but if i try:

mycoll.find({ $text: { $search: "uper"} })

I dont get any results.

My Question: If there is a way to use $text so its finds results with a part of the searching string? (e.g. like '%uper%' in mysql)

Attention: I dont ask for a regex only search - i ask for a regex-search within a $text-search!

Check this : http://stackoverflow.com/questions/3305561/how-to-query-mongodb-with-like — Sikorski, Jun 27 '14 at 09:05

score 51 · Accepted Answer · edited Jun 11 '21 at 15:13

51

It's not possible to do it with $text operator.

Text indexes are created with the terms included in the string value or in an array of strings and the search is based in those indices.

You can only group terms on a phrase but not take part of them.

Read $text operator reference and text indexes description.

edited Jun 11 '21 at 15:13

Christopher Moore

15,626
10
42
52

answered Jun 27 '14 at 09:05

francadaval

2,451
3
26
36

Sure you can. See my answer. – Markus W Mahlberg Jun 27 '14 at 14:48
7

Not with $text operator and text indexes. – francadaval Jun 27 '14 at 20:08
1

Live from the docs: "If an index exists for the field, then MongoDB matches the regular expression against the values in the index, which can be faster than a collection scan." – Markus W Mahlberg Jun 27 '14 at 21:13
6

As I said, not with $text operator. – francadaval Jun 28 '14 at 06:51
I'd appreciate a documentation link or the output of an according `explain()` method. – Markus W Mahlberg Jun 28 '14 at 09:29
3

Back to that topic after 2 years. Did anything change? I mean, no way to perform a substring matching with a `$text` operator? @MarkusWMahlberg I confirm the answer of @francadaval. You can use regex to search for partial matching, but the performance is not good, even with an index which prevents COLLSCAN. :( – floatingpurr Feb 21 '17 at 12:44
2

He's saying you cannot do it, how is this the correct solution? – mskw Jun 02 '17 at 13:45
It's the correct answer not the correct solution since there isn't a solution using `$text`. – francadaval Jun 02 '17 at 18:22
2

what is solution for this. I want both full text search functionality and partial search – Prashant Tapase Jun 16 '17 at 09:39
@Markus W Mahlberg answer below is the right one. You can use regexp. – JuanGG May 19 '21 at 09:24

Jean-Baptiste Martin · Answer 2 · 2022-10-23T11:00:23.110

18

The best solution is to use both a text index and a regex.
The index will provide excellent speed performances but won't match as many documents as a regex.
The regex will allow a fallback in case the index doesn't return enough results.

db.mycoll.createIndex({ foo: 'text' });
db.mycoll.createIndex({ foo: 1 });
db.mycoll.find({
  $or: [
    { $text: { $search: 'uper' } },
    { foo: { $regex: 'uper' } }
  ]
});

For even better performances (but slightly different results), use ^ inside the regex:

db.mycoll.find({
  $or: [
    { $text: { $search: 'uper' } },
    { foo: { $regex: '^uper' } }
  ]
});

edited Oct 23 '22 at 11:00

answered Sep 10 '17 at 11:17

Jean-Baptiste Martin

1,399
1
10
19

1

The above solution doesn't work. Throwing an error: No query solutions – Mohit Bhansali Oct 07 '17 at 06:52
Removed an extra foo after collection name. Should work now! – Jean-Baptiste Martin Oct 08 '17 at 08:00
the regex search would also find those results that text search finds... isn't this better with just regex search?? – user1955934 Nov 13 '17 at 11:59
@user1955934 You will want to use a text search to speed up your query, since foo will be matched against an index. Consider the regex as a plan B in case you can't find enough results matching your index. – Jean-Baptiste Martin Nov 13 '17 at 15:45
Ensure you have both indexes if it gives you 'No query solutions' exceptions – veb May 28 '18 at 14:20
2

Do we have something similar that could work with aggregations as well? – PrivateOmega Jun 26 '21 at 13:26

score 15 · Answer 3 · 2014-06-28T17:29:12.133

What you are trying to do in your second example is prefix wildcard search in your collection mycoll on field foo. This is not something the textsearch feature is designed for and it is not possible to do it with $text operator. This behaviour does not include wildcard prefix search on any given token in the indexed field. However you can alternatively perform regex search as others suggested. Here is my walkthrough:

>db.mycoll.find()
{ "_id" : ObjectId("53add9364dfbffa0471c6e8e"), "foo" : "my super cool item" }
{ "_id" : ObjectId("53add9674dfbffa0471c6e8f"), "foo" : "your not so cool item" }
> db.mycoll.find({ $text: { $search: "super"} })
{ "_id" : ObjectId("53add9364dfbffa0471c6e8e"), "foo" : "my super cool item" }
> db.mycoll.count({ $text: { $search: "uper"} })
0

The $text operator supports search for a single word, search for one or more words or search for phrase. The kind of search you wish is not supported

The regex solution:

> db.mycoll.find({foo:/uper/})
{ "_id" : ObjectId("53add9364dfbffa0471c6e8e"), "foo" : "my super cool item" }
>

The answer to your final question: to do mysql style %super% in mongoDB you would most likely have to do:

db.mycoll.find( { foo : /.*super.*/ } );

Thank you for your detailed answer. I want to use the $text-feature because of "Text Score" and Language-Fields. But i understand form @rancadaval answer that $text only work with strings. — mdunisch, Jun 28 '14 at 12:49
@user3351722 yes, with `$text` you can search for a word, a list of words or phrases. I updated the answer as well. — , Jun 28 '14 at 17:30

score 12 · Answer 4 · edited May 23 '17 at 12:26

It should work with /uper/.

See http://docs.mongodb.org/manual/reference/operator/query/regex/ for details.

Edit:

As per request in the comments:

The solution wasn't necessarily meant to actually give what the OP requested, but what he needed to solve the problem.

Since $regex searches don't work with text indices, a simple regex search over an indexed field should give the expected result, though not using the requested means.

Actually, it is pretty easy to do this:

db.collection.insert( {foo: "my super cool item"} )
db.collection.insert( {foo: "your not so cool item"})
db.collection.ensureIndex({ foo: 1 })
db.collection.find({'foo': /uper/})

gives us the expected result:

{ "_id" : ObjectId("557f3ba4c1664dadf9fcfe47"), "foo" : "my super cool item" }

An added explain shows us that the index was used efficiently:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.collection",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "foo" : /uper/
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "filter" : {
                    "foo" : /uper/
                },
                "keyPattern" : {
                    "foo" : 1
                },
                "indexName" : "foo_1",
                "isMultiKey" : false,
                "direction" : "forward",
                "indexBounds" : {
                    "foo" : [
                        "[\"\", {})",
                        "[/uper/, /uper/]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        // skipped
    },
    "ok" : 1
}

To make a long story short: No, you can not reuse a $text index, but you can do the query efficiently. Like written in Implement auto-complete feature using MongoDB search , one could probably be even more efficient by using a map/reduce approach, eliminating redundancy and unnecessary stop words from the indices, at the cost of being not real time any more.

That's the `$regex` operator, which is not what OP asked about. It seems the `$text` operator does not support wildcards. — zjm555, Feb 19 '15 at 19:07
@zjm555: Well, it might be a solution to OPs problem in the first place. That's what is called "creative problem solving" and might be worth a try. ;) — Markus W Mahlberg, Feb 22 '15 at 19:49
Would like to know the reason for down voting. Did I miss something? — Markus W Mahlberg, Apr 30 '15 at 05:43
$search operator doesnt support regex. It only supports string which doesn't do a substring search on indexed fields. That's why it doesn't work. I tried it so you might as well should once to verify your answer — deepak, Jun 05 '15 at 21:40
This query doesn't use index efficiently, whole collection has to be scanned in worst scenario. Use `explain('executionStats')` and find out yourself... or just look at the index bounds in your answer. Such index is efficient only if you query for a prefix. — Sebastian Nowak, Feb 25 '16 at 01:10
Also: this can only work "properly" with single field indexes. If you have index on `{foo: "text", bar: "text"}` and want to search in both of them you have to use `.find({$text: {$search: query}})` and then your answer won't work. — Enethion, May 04 '16 at 11:17

score 3 · Answer 5 · edited Aug 10 '18 at 22:24

3

As francadaval said, text index is searching by terms but if you combine regex and text-index you should be good.

mycoll.find({$or: [ 
  { 
    $text: {
      $search: "super"
    }
  },
  {
    'column-name': {
      $regex: 'uper',
      $options: 'i'
  }
]})

Also, make sure that you have normal index applied to the column other than text index.

edited Aug 10 '18 at 22:24

Victor Schröder

6,738
2
42
45

answered Dec 20 '16 at 18:48

jasenkoh

4,011
2
19
24

it didn't work on mongo 3.2 . Which version did you test this query ? – oblivion Jan 09 '17 at 07:08
3.2.x, you need to create text index before you can use it, https://docs.mongodb.com/v3.2/core/index-text/#create-text-index – jasenkoh Jan 09 '17 at 13:38
2

Not working for me. Getting error: Error: error: { "waitedMS" : NumberLong(0), "ok" : 0, "errmsg" : "unknown top level operator: $regex", "code" : 2 } – Arijit Jan 26 '17 at 09:51
2

Even if that worked, the input that we have is "uper". There is no way to guess "super" from "uper". This solution doesn't match the problem. – Sergey Shcherbakov Apr 18 '17 at 13:06
1. `}` missing before `]`. 2. Failed to produce a solution for TEXT under OR-other non-TEXT clauses under OR have to be indexed as well – Prashant Tapase Jun 16 '17 at 09:37
Is there any solutions for search value like this `super cool` – sankar muniyappa Jan 19 '18 at 07:42
Omg, what kind of indentation/formatting is this... Fixing. – Victor Schröder Aug 10 '18 at 22:20
I think regex case insensitive queries can't use indexes as per official docs https://docs.mongodb.com/manual/reference/operator/query/regex/#index-use – Irfan May 19 '19 at 11:44

score 1 · Answer 6 · answered Mar 25 '19 at 05:01

1

if you go with regex you can achieve search for "super cool" but not "super item", to achieve both request do an or request with $text and $regex for the search term.

make sure you index both text indexing and normal indexing to work.

answered Mar 25 '19 at 05:01

Porika Venkatesh

11
1

score 0 · Answer 7 · answered Feb 07 '18 at 10:44

0

You could have achieved is as-

db.mycoll.find( {foo: { $regex :  /uper/i  } })

Here 'i' is an option, denotes case-insensitive search

answered Feb 07 '18 at 10:44

mohit_IBS

161
5

mongoDB prefix wildcard: fulltext-search ($text) find part with search-string

7 Answers7

Linked