英文:
How to use regex in foreignkey in mogodb lookup
问题
我应该如何在外键字段上使用通配符进行$lookup？
任务是从Transaction中检索所有记录，其中desc子字符串在Keyword集合中未找到。
Transaction中的desc可能以abc<keyword>def的形式出现，与Keyword集合交叉引用，如果找到
我考虑使用否定匹配的正则表达式.*XXX.*来完成任务。
不确定如何在$lookup中放置正则表达式子句。
Transaction集合：
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集合：
keyword=[
{
"_id": {
"$oid": "64816f3828372d84a93cd4ad"
},
"code": 123,
"desc": "Key1"
},
{
"_id": {
"$oid": "648174bf28372d84a93cd4b5"
},
"code": 456,
"desc": "Key2",
}
]
我知道我可能需要使用let和pipeline...但不确定如何将它们组合在一起。
$lookup:{
from: "keyword",
let: {
desc:'$desc'
},
pipeline: [
....?
]
as: "result"
}
英文:
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 <keyword> 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"
}
答案1
得分: 1
一种方法是在 $lookup 中使用 $regexMatch。像这样：
db.transactions.aggregate([
{
"$lookup": {
"from": "keyword",
"let": {
desc: "$desc"
},
"pipeline": [
{
$match: {
$expr: {
"$regexMatch": {
"input": "$$desc",
"regex": {
"$concat": [
".*",
"$desc",
".*"
]
}
}
}
}
}
],
"as": "matchingDocs"
}
},
{
"$match": {
matchingDocs: []
}
}
])
英文:
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: []
}
}
])
答案2
得分: 0
你可以将关键字作为变量获取，然后使用该关键字连接关键字。我在这里提取关键字，假设它将始终是abc<key>def。最后，它会过滤掉没有匹配关键字的记录。
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: []
}
}
}
])
英文:
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 abc<key>def. 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: []
}
}
}
])
答案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"
}
}
])
英文:
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"
}
}
])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论