Question 185280 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – How to use regex in foreignkey in mogodb lookup

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"
}
Tags:

3

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    You can get the key as a variable and then join the keywords using the key.
    I am extracting the key here, under the assumption that it will always be abcdef. At the end it filter out records which does not have matching keywords.

    https://mongoplayground.net/p/B0SIHY68gz0

    db.transactions.aggregate([
  {
    $lookup: {
      from: "keywords",
      let: {
        keywordStr: {
          $substr: [
            "$desc",
            3,
            {
              $subtract: [
                {
                  $strLenCP: "$desc"
                },
                6
              ]
            }
          ]
        }
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$desc",
                "$$keywordStr"
              ]
            }
          }
        }
      ],
      as: "keywords"
    }
  },
  {
    $match: {
      keywords: {
        $ne: []
      }
    }
  }
])
    Login or Signup to reply.
  3. 0 
    db.Transaction.aggregate([
  {
    $lookup: {
      from: "Keyword",
      let: { keywordSubstring: { $regexFind: { input: "$desc", regex: "<keyword>", options: "i" } } },
      pipeline: [
        {
          $match: {
            $expr: {
              $not: {
                $regexMatch: {
                  input: "$$keywordSubstring.match",
                  regex: { $concat: [".*", { $escapeRegex: "$$keywordSubstring.match" }, ".*"] },
                  options: "i"
                }
              }
            }
          }
        }
      ],
      as: "keywordMatches"
    }
  }
])
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search