1

Since Mongo only supports one $text field per aggregation pipeline (inside the first $match stage), that means you can't perform a logical AND, since you can't $and the results of multiple $text searches.

// Fails due to "too many text expressions"
db.Employees.aggregate([
    {$match: {$and: [
        {$text: {$search: "senior"}},
        {$text: {$search: "manager"}}
    ]}}
])

Therefore I need to perform multiple separate $text searches, combine the results in my NodeJS code, and pass that result set back into an aggregation pipeline for further processing (e.g. $addFields, $match, $sort).

Is there a way to do something like...

let results1 = db.Employees.find({"$text":{"$search":"senior"}}, {"score":{"$meta":"textScore"}})
let results2 = db.Employees.find({"$text":{"$search":"manager"}}, {"score":{"$meta":"textScore"}})
let combinedResults = _.intersectionWith(results1, results2, _.isEqual)
let finalResults = /* pass combinedResults into aggregation pipeline and execute it */

Something like the opposite of the $out operator, where I'm reading in a result set instead.

I'm using NestJS and Mongoose if that helps.

yev
  • 65
  • 1
  • 6
  • Are you looking for phrase kind of search where document contains `Senior manager`? – Gibbs Aug 14 '20 at 04:31
  • I've looked into the phrase search https://docs.mongodb.com/manual/reference/operator/query/text/#phrases but I want it to match even if the terms aren't right next to each other - so the document should contain `senior` in some field, and `manager` in some field. They could be within the same field, but they don't have to be. Better example might be a search like "James AND manager" – yev Aug 14 '20 at 14:23
  • Is there a way to directly feed a predefined array of documents into an aggregation pipeline though? – yev Aug 14 '20 at 14:26
  • Unfortunately only one text is allowed per aggregation. Are you ok to use regex? – Gibbs Aug 14 '20 at 15:39
  • I could be down for some regex - would that let me stick everything into an aggregation? – yev Aug 14 '20 at 19:01

1 Answers1

0

There are restrictions in $text, that you already know,

There is a option if you have limited fields, using $regexMatch, I am not sure, in how many fields you have text index, but with this you can combine match conditions with $and operator for multiple fields,

Example Data:

[
  { _id: 1, f1: "senior", f2: "manager" },
  { _id: 2, f1: "junior", f2: "manager" },
  { _id: 3, f1: "fresher", f2: "developer" },
  { _id: 4, f1: "manager", f2: "senior" }
]

Aggregation Query 1:

  • $addFields to add new field matchResult for matching status in boolean
db.collection.aggregate([
  {
    $addFields: {
      matchResult: {
        $and: [
  • first $or condition match if f1 or f2 fields match senior then return true other wise return false
          {
            $or: [
              { $regexMatch: { input: "$f1", regex: "senior", options: "x" } },
              { $regexMatch: { input: "$f2", regex: "senior", options: "x" } }
            ]
          },
  • second $or condition match if f1 or f2 fields match manager then return true other wise return false
          {
            $or: [
              { $regexMatch: { input: "$f1", regex: "manager", options: "x" } },
              { $regexMatch: { input: "$f2", regex: "manager", options: "x" } }
            ]
          }

        ]
      }
    }
  },
  • $match condition return result that have matchResult is equal to true
  { $match: { matchResult: true } }
])

Playground

Aggregation Query 2:

  • if you are not using array fields then this is sort way, directly you can concat all fields on one field, here i have merged f1 and f2 with space in allField
db.collection.aggregate([
  {
    $addFields: {
      allField: { $concat: ["$f1", " ", "$f2"] }
    }
  },
  • this will match $and condition on both word match if both true then return true otherwise false
  {
    $addFields: {
      matchResult: {
        $and: [
          { $regexMatch: { input: "$allField", regex: "senior", options: "x" } },
          { $regexMatch: { input: "$allField", regex: "manager", options: "x" } }
        ]
      }
    }
  },
  • $match condition return result that have matchResult is equal to true
  { $match: { matchResult: true } }
])

Playground

Note: This is alternate approach for limited fields, but imaging if more then 5 fields then it affects speed and performance of the query.

turivishal
  • 34,368
  • 7
  • 36
  • 59
  • Dang, the biggest problem is that we have lots of fields on a document, so $text is basically necessary to search all of them at once (and do all of its fancy word stemming, etc.) – yev Aug 17 '20 at 13:26
  • I can just keep using multiple `find()` operations with `$text` to get the results then. Back to the main question then I guess -- I have more unrelated processing stages further down the aggregation pipeline (mainly more `$addFields` to modify the result score based on other criteria). Is there a stage to import the `$text` results into that pipeline, or do aggregations always have to start with the entire collection? – yev Aug 17 '20 at 13:30
  • yes you are right, that i already mentioned, this is a option for limited fields, and with $text its is not possible. – turivishal Aug 17 '20 at 13:30
  • i don't know but might be your 2 queries are good solution.. and there are many restriction on $text you can read in question link. – turivishal Aug 17 '20 at 13:33