MongoDB多关联集合查询：添加provider名称正则匹配条件

我有users、providers、data三个MongoDB集合，现有聚合查询已通过$lookup关联users集合，能匹配username以'leo'开头的记录。现在需要给查询添加provider.name以'prov3'开头的正则匹配条件，已经关联了providers集合，但不知道怎么配置这个条件。

集合结构 #

users集合 #

[
    { _id: 1, username: 'mark' },
    { _id: 2, username: 'leon' },
    { _id: 3, username: 'leontin'}
]

providers集合 #

[
    { _id: 1, name: 'prov1' },
    { _id: 2, name: 'prov2' },
    { _id: 3, name: 'prov3' },
    { _id: 39, name: 'prov39' }
]

data集合 #

[
    {
      _id: ObjectId('6675309f82bdfbe3b00febfa'),
      user: 1,
      provider: 1,
      message: '94451 Laos Sweden Iran',
    },
    {
      _id: ObjectId('6675309f82bdfbe3b00febfb'),
      user: 2,
      provider: 3,
      message: 'message  22222',
    },
    {
      _id: ObjectId('6675309f82bdfbe3b00febfc'),
      user: 3,
      provider: 39,
      message: 'message  3333',
    }
]

现有查询代码 #

已关联users并正常运行的查询 #

db.data.aggregate([
  {
    "$lookup": {
      "from": "users",
      "localField": "user",
      "foreignField": "_id",
      "as": "user_info"
    }
  },
  {
    "$unwind": "$user_info"
  },
  {
    $match: {
      $expr: {
        $regexMatch: {
          input: "$user_info.username",
          regex: "^leo",
          options: "i"
        }
      }
    }
  }
])

已关联providers但未添加匹配条件的查询 #

db.data.aggregate([
  {
    "$lookup": {
      "from": "users",
      "localField": "user",
      "foreignField": "_id",
      "as": "user_info"
    }
  },
  {
    "$unwind": "$user_info"
  },
  {
    "$lookup": {
      "from": "providers",
      "localField": "provider",
      "foreignField": "_id",
      "as": "provider_info"
    }
  },
  {
    "$unwind": "$provider_info"
  },
  {
    $match: {
      $expr: {
        $regexMatch: {
          input: "$user_info.username",
          regex: "^leo",
          options: "i"
        }
      }
    }
  }
])

解决方案 #

要添加provider.name以'prov3'开头的匹配，只需要在$match阶段的$expr里用$and组合两个$regexMatch条件即可，其中第二个条件针对$provider_info.name字段：

db.data.aggregate([
  {
    "$lookup": {
      "from": "users",
      "localField": "user",
      "foreignField": "_id",
      "as": "user_info"
    }
  },
  {
    "$unwind": "$user_info"
  },
  {
    "$lookup": {
      "from": "providers",
      "localField": "provider",
      "foreignField": "_id",
      "as": "provider_info"
    }
  },
  {
    "$unwind": "$provider_info"
  },
  {
    $match: {
      $expr: {
        $and: [
          {
            $regexMatch: {
              input: "$user_info.username",
              regex: "^leo",
              options: "i"
            }
          },
          {
            $regexMatch: {
              input: "$provider_info.name",
              regex: "^prov3",
              options: "i"
            }
          }
        ]
      }
    }
  }
])

这个查询会同时筛选出：

• 关联用户的username以'leo'开头（不区分大小写）
• 关联服务商的name以'prov3'开头（不区分大小写）

的data记录。

内容的提问来源于stack exchange，提问作者amic