How to use $text search inside $lookup pipeline

Viewed 1052

I have the next collection for exaple:

// vehicles collection

[
  {
    "_id": 321,
    manufactor: SOME-OBJECT-ID
  },
  {
    "_id": 123,
    manufactor: ANOTHER-OBJECT-ID
  },
]

And I have a collection named tables:

// tables collection
[
  {
    "_id": SOME-OBJECT-ID,
    title: "Skoda"
  },
  {
    "_id": ANOTHER-OBJECT-ID,
    title: "Mercedes"
  },
]

As you can see, the vehicles collection's documents are pulling data from the tables's collection ducments - the first document in the vehicles collection has a manufactor id which is getting pulled from the tables collection and named Skoda.

That is great.

When I am querying the DB using aggregate I can able to easily pull the remote data from the remote collections respectively - without any problem. I can also easily make rules and limitations like $project, $sort, $skip, $limit and others.

But I want to display to the user only those vehicles that are manufcatord by Mercedes. Since Mercedes is not mentioned in the vehicles collection, but only its ID, the $text $search would not return with the right results.

This is the aggregate pipeline that I provide:

[
  {
    $match: {
      $text: {
        $search: "Mercedes"
      }
    }
  },
  {
    $lookup: {
      from: "tables",
      let: {
        manufactor: "$manufactor"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$_id", "$$manufactor"
              ]
            }
          }
        },
        {
          $project: {
            title: 1
          }
        }
      ],
      as: "manufactor"
    },
  },
  {
    $unwind: "$manufactor"
  },
  {
    $lookup: {
      from: "tables",
      let: {
        model: "$model"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$_id", "$$model"
              ]
            }
          }
        },
        {
          $project: {
            title: 1
          }
        }
      ],
      as: "model"
    },
  },
  {
    $unwind: "$model"
  },
  {
    $lookup: {
      from: "users",
      let: {
        joined_by: "$_joined_by"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$_id", "$$joined_by"
              ]
            }
          }
        },
        {
          $project: {
            personal_info: 1
          }
        }
      ],
      as: "joined_by"
    },
  },
  {
    $unwind: "$joined_by"
  }
]

As you can see I am using the $text and $search $match at the first stage in the pipleline - otherwise MongoDB will throw an error.

But this $text $search object searhed only in the origin collection - the vehicles collection.

Is there a way to tell MongoDB to search in the remote collection with the $text and $search method and then put in the aggregate only results that are matching both?

UPDATE

When I am doing this instead:

     {
      $lookup: {
        from: "tables",
        pipeline: [
          {
            $match: {
              $text: {
                $search: "Mercedes"
              }
            }
          },
          {
            $project: {
              title: 1
            }
          }
        ],
        as: "manufactor"
      },
    },

This is what I receive:

 MongoError: pipeline requires text score metadata, but there is no text score available
1 Answers

if you are using one of the affected versions in this thread, you need to update your mongodb server. As you can see the issue was fixed in version 4.1.8

Related