如何在mongodb中的引用字段中使用$ regex搜索 [英] how to use $regex search in referenced field in mongodb

问题描述

我正在为一项任务而苦苦挣扎，因为我使用了两种模式

i am struggling with a task, which it is I used two schemas

User Schema
{
  "first_name": String,
  "last_name":String,
  "address": String
}

Employee schema
{
  user:{
      type: ObjectId,
      ref: 'User'
  },
  gross_pay: String,
  net_pay: String
  tax: String,

}

那么，如何在此用户引用字段的Employee Schema中使用$ regex搜索first_name?我尝试了很多方法，无法做到.我该如何解决?预先感谢

Well, how can I search first_name using $regex in Employee Schema in this user referenced field? I tried in so many ways, can't get it. how can i resolve it? Thanks in advance

推荐答案

第一种方法:

使用 $lookup 聚合

Employee.aggregate([
  { "$lookup": {
    "from": "users",
    "let": { "user": "$user" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$_id", "$$user"] }}},
      { "$project": { "firstName": 1 }}
    ],
    "as": "user"
  }},
  { "$unwind": "$user" },
  { "$match": { "user.firstName": { "$regex": your_string, "$options": "i" }}}
])

但这不是更好的方法，因为 阶段将应用于所有Employee文档，然后 $match 给用户firstName会使查询速度变慢.

But it is not the better approach as the $lookup stage is being applied to all the Employee documents and then $match to the users firstName makes the query bit slow.

第二种方法:

首先使用$regex查找具有firstName等于匹配字符串的用户，然后在Employee集合中找到_id.

First find the users having firstName equal to the match string using $regex and then find the _id in the Employee collection.

const userIds = await (Users.find({ "firstName": { "$regex": your_string, "$options": "i" } })).map(user => user._id)

const employees = await Employee.find({ "user": { "$in": userIds }})

第三种方法:

在employee架构中保持user架构的单键firstName

Keep the single key firstName of the user schema in the employee schema

员工架构

  user: { type: ObjectId, ref: 'User' },
  gross_pay: String,
  net_pay: String
  tax: String
  firstName: String

然后直接使用查询

const userIds = await Employee.find({
  "firstName": { "$regex": your_string, "$options": "i" }
})

这篇关于如何在mongodb中的引用字段中使用$ regex搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！