mongoDB $lookup with $search pipeline

I have two collections:

store_group collection:

    {"_id": ObjectId("674fe7cc4e65df54a0db23b5"),
     "stores": [
      {"id": 101, "name":"abc"},  // 101 as int
      {"id": 204, "name":"cd"} // 204 as int
    ]}

location_view_for_search, a view created from stores collection:

    {"_id": ObjectId("67400d3997214dc99c6311c5"),
      "location_number": "101", // 101 as String
    }
    {"_id": ObjectId("67400d3997214dc99c6311c6"),
      "location_number": "204", // 204 as String
    }

I need to lookup store data from store_group, via location_number and apply autocomplete operation on location_number. because the stores._id in store_group collection is number and location_view_for_search.location_number is string, so I first map the number to string, the do the lookup:

    [
      {
        $addFields:
          {
            storeIds: {
              $map: {
                input: "$stores",
                in: {
                  $toString: "$$this.id"
                }
              }
            }
          }
      },
      {
        $lookup:
          {
            from: "store_view_for_search",
            localField: "storeIds",
            foreignField: "location_number",
            as: "store_details"
          }
      }
    ]

till now it's all working fine, i can see the pipeline output showing the store_details correctly:

    {
      "stores": [
        {"id": 101, "name": "abc"},
        {"id": 204, "name": "cd"}
      ],
      "storeIds":["101", "204"],
      "store_details": [
        {"_id": ObjectId("67400d3997214dc99c6311c5"), "location_number": "101"},
        {"_id": ObjectId("67400d3997214dc99c6311c6"), "location_number": "204"}
      ]
    }

next I added $search inside $lookup to apply autocomplete search on location_number in location_view_for_search, i've created the atlas search index to set static mapping on location_number to be autocomplete.

    {
        $lookup:
          {
            from: "location_view_for_search",
            localField: "storeIds",
            foreignField: "location_number",
            as: "store_details",
            pipeline: [
              {
                $search: {
                  index:
                    "autocomplete-by-location-number",
                  compound: {
                    should: [
                      {
                        autocomplete: {
                          query: "572",
                          path: "location_number",
                          fuzzy: {
                            maxEdits: 1,
                            prefixLength: 3,
                            maxExpansions: 1
                          }
                        }
                      }
                    ],
                    minimumShouldMatch: 1
                  }
                }
              }
            ]
          }
      }

then all of the sudden the pipeline output showing the store_details in the result as empty array. what did i do wrong? I'm following the mongo tutorial here https://www.mongodb.com/docs/atlas/atlas-search/tutorial/lookup-with-search/.

I've also tested the same $search on location_view_for_search view, it works fine, I can search locations by autocomplete search on location_number

This is about the behaviour of the Correlated Subqueries Using Concise Syntax that you are using. In other words, the combined use of localField & foreignField + pipeline. You can think like adding a $match stage at the start of the subpipeline with $eq. However, your storeIds is an array so the $eq won't work and a $in is needed instead.

So you can do something like below:

1. use let in the $lookup to assign a variable, say named sids
2. remove the localField and foreignField option
3. start your pipeline with your $search
4. put a $match stage with $in with sids after the $search

{
    $lookup: {
      from: "location_view_for_search",
      let: {
        sids: "$storeIds"
      },
      as: "store_details",
      pipeline: [
        {
          $search: {
            // your $search here
          }
        },
        {
          "$match": {
            $expr: {
              $in: [
                "$location_number",
                "$$sids"
              ]
            }
          }
        }
      ]
    }
  }

Mongo Playground to demonstrate full syntax. The query won't run in playground since $search is not supported there.