Is it possible to implement search in a lookup stage or match stage for joined collection

[Franklin_Isaiah]

Hello, I am trying to do a nested search on an joined collection using aggregation but from the documentation, I can use $text on the root collection, nothing on the nested collection. Is it possible to achieve this?

Here’s my code

matchStage := bson.D{{"$match", bson.D{{"user_id", userID}}}}
lookupStage := bson.D{{"$lookup", bson.D{
	{"from", "members"},
	{"localField", "member_id"},
	{"foreignField", "_id"},
	{"as", "members"}},
}}
cursor, cursorErr := r.connect.User.Aggregate(ctx, mongo.Pipeline{
	matchStage, lookupStage,
})

regular search looks like this

matchStage = bson.D{{"$match", bson.D{
	{"user_id", userID},
	{"$text", bson.D{{"$search", search}}},
}}}

but this will search the users table but what I want to search is the members table I just joined in the lookup, how do i achieve this please?

[slava]

Hello, @Franklin_Isaiah ! Welcome to the MongoDB community!

Indeed, with $text operator you can query the documents from one collection and you can not use it within aggregation pipeline.

To be able to perform text search in joined documents from other collections, you need to use Atlas Search, which provides $search stage just for this case. As per documentation, $search must be the very first stage of any pipeline. That means, that we can not use it before $lookup stage, but we can use it the $lookup’s pipeline.

I will demonstrate how it works with the examples below.

First, we create test data:

db.groups.insertMany([
  {
    _id: 'G1',
    members: ['M1', 'M2', 'M3']
  },
  {
    _id: 'G2',
    members: ['M4', 'M5']
  },
]);

db.members.insertMany([
  {
    _id: 'M1',
    name: 'Fred Weasley'
  },
  {
    _id: 'M2',
    name: 'Seamus Finnigan'
  },
  {
    _id: 'M3',
    name: 'Arthur Weasley'
  },
  {
    _id: 'M4',
    name: 'Neville Longbottom'
  },
  {
    _id: 'M5',
    name: 'Percy Weasley'
  }
]);

Then, we can join group members and filter them by name with the $search stage.

db.groups.aggregate([
  {
    $lookup: {
      from: 'members',
      let: {
        membersIds: '$members',
      },
      pipeline: [
        {
          $search: {
            text: {
              query: 'Weasley',
              path: 'name'
            }
          }
        },
        {
          $match: {
            $expr: {
              $in: ['$_id', '$$membersIds']
            }
          }
        }
      ],
      as: 'members',
    }
  }
]);

Output:

[
  {
    _id: 'G1',
    members: [
      { _id: 'M3', name: 'Arthur Weasley' },
      { _id: 'M1', name: 'Fred Weasley' }
    ]
  },
  {
    _id: 'G2', 
    members: [
      { _id: 'M5', name: 'Percy Weasley' }
    ]
]

Note, that in order $search stage worked, you need to define proper index on Atlas. I used this one:

{
  "mappings": {
    "dynamic": false,
    "fields": {
      "name": {
        "type": "string"
      }
    }
  }
}