How to use regex in foreignkey in mogodb lookup

Question

How can I do a $lookup using wildcard on the foreign key field?

The task is to retrieve all records from Transaction where the the desc substring is not found in the Keyword collection.

The desc in Transaction could come in the form of abc<keyword>def, and cross referencing to Keyword collection, this should be a match if is found.

I thought of using a negative match for regex .*XXX.* to achieve the task.

Not sure how to put a regex clause in a $lookup.

Transaction collection:

transactions=[
{
  "_id": {
    "$oid": "6480267ab9fe78e82131b737"
  },
  "date": {
    "$date": "2020-06-22T00:00:00.000Z"
  },
  "desc": "abcKey1def",
},
{
  "_id": {
    "$oid": "6480267ab9fe78e82131b738"
  },
  "date": {
    "$date": "2020-06-23T00:00:00.000Z"
  },
  "desc": "abcdef",
}
]

Keyword collection:

keyword=[
{
  "_id": {
    "$oid": "64816f3828372d84a93cd4ad"
  },
  "code": 123,
  "desc": "Key1"
},
{
  "_id": {
    "$oid": "648174bf28372d84a93cd4b5"
  },
  "code": 456,
  "desc": "Key2",
}
]

I know I probably need to use let and pipeline... but not sure how to put them together.

$lookup:{
  from: "keyword",
  let: {
    desc:'$desc'
  },
  pipeline: [
     ....?
  ]
  
  as: "result"
}

Answer 1

One way is to use $regexMatch in $lookup. Like this:

db.transactions.aggregate([
  {
    "$lookup": {
      "from": "keyword",
      "let": {
        desc: "$desc"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              "$regexMatch": {
                "input": "$$desc",
                "regex": {
                  "$concat": [
                    ".*",
                    "$desc",
                    ".*"
                  ]
                }
              }
            }
          }
        }
      ],
      "as": "matchingDocs"
    }
  },
  {
    "$match": {
      matchingDocs: []
    }
  }
])

Playground link.