How can I utilize a 'let' variable in the $search operator of a lookup?

I have a search bar that needs to perform a conditional search where some of the conditions depend on another collection’s value. I have text input so I need to use the search index, rather then connecting via foreign/local id — since $search needs to happen as the first stage in the lookup pipeline.

From the initial collection, I find all the documents that satisfy the initial conditions, then perform a lookup to the other collection based on those results.

Let’s say this is what a doc looks like just before lookup:

{
col1_id: "123",
col2_id: "abc",
<metadata_CONT'D>
}

Using the id that connects us to the other collection,

$lookup: {
   "from": "col2",
   "let": {
      "id": "$col2_id",
      <other variables>
   },
   "pipeline": [
      {
         "$search": {
            "index": "search_index",
            "compound": {
              "must": [
                  {
                      "phrase": {
                          "path": "col2_id",
                          "query": "$$id"
                      }
                  },
                  <other search conditions>
              ],
            }
          }
        },
      ],
      "as": "col2_result"
   }

When I deliberately replace “$$id” with “abc”, the search and lookup work…so it’s just the $$let_variable that isn’t working. I’ve scoured the internet but google refuses to relinquish its secrets. I’m sure this is answered somewhere but I couldn’t find it in community, either, so can someone answer my question:

Is this possible? — Using a let variable in the $search of a lookup?

I’ve tried messing with the variable names and single $ as opposed to double $$, and random things like that, but nothing is working, so I’m led to believe it simply doesn’t work this way because of how it compiles when you run the query and it evaluates the searches and whatnot.

Anyway, any help would be appreciated.

At this point, I’m thinking of just getting all the results from the first bit of the query, then running a forEach and doing an aggregation on col2 with the doc.col2_id!

Hi @Charlie_Buyas :wave:

I believe the behaviour you’ve described is detailed in the following SERVER ticket : https://jira.mongodb.org/browse/SERVER-71036

My understanding is that at this stage the variables are not being resolved. I suggest monitoring this ticket for any changes but I believe the workaround you’ve described should work also.

Regards,
Jason

Thank you for your reply. At least I confirmed my suspicions — it’s not just me. Thanks for linking the ticket, I’ll keep an eye on it.

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.