4

Full text search in MongoDB seems to be a nice feature, especially when it comes to the need of high performance search and indexes. However, I am wondering why a full text search is not allowed over more than one collection.

For example: I have a supplier and a products collection (every supplier has n products) I would really like to do a search over all products, but the name of the supplier should also be matched. Right now I am doing this with a $lookup and then a match with a regular expression. This works, but its very slowly (500 - 800ms).

If I used a full text search with an index, this would increase performance significantly. But the $text-operator has to be the first stage in the aggregation pipeline, not allowing me to use a $lookup first.

(See restrictions-section: https://docs.mongodb.com/manual/reference/operator/query/text/#op._S_text)

Any ideas how I could increase the speed of a text search over multiple collections?

Community
  • 1
  • 1
David
  • 1,275
  • 2
  • 17
  • 27

1 Answers1

2

for someone who still looking for solution

db.getCollection('projects').aggregate([{
    "$match": {
        "$text": {"$search": query }
    }}, {
    "$lookup": {
        "from":         "users",
        "localField":   "uuid",
        "foreignField": "uuid",
        "as":           "user"
    }}, {
        "$sort": {
            "score": { "$meta": "textScore" }
        }
    }]);

where query is a text search string.

This query is for projects collection with key uuid, which refers to users collection with uuid key, all sorted by relevance.

Ivan Ivanov
  • 2,076
  • 16
  • 33
  • A very useful example! Furthermore, adding `"$addFields": {"score": { "$meta": "textScore" }}` to the array shows the `score` field in the result. – Richard Osseweyer Apr 19 '22 at 15:15