node.js - MongoDB $lookup 管道匹配 _id 不起作用

Question

我正在尝试做一个 $lookup在一个集合中并将一些数据添加到我的文档中。问题是当我尝试匹配我的 $lookup 时管道来自 _id它返回一个空数组。这是我的代码:

Schedule.aggregate([{ // My Schedule schema
  $match: {
    store: req.query.store,
    "customer.id": req.query.user
  }
},
{
  $skip: +req.query.skip
}, {
  $limit: +req.query.limit
},
{
  $lookup: {
    from: Employee.collection.name, // "employee" schema,
    let: {
      id: "$employee.id" // employee _id from the "schedule" collection match above
    },
    pipeline: [{
        $match: {
          $expr: {
            "_id": "$$id" // here i try to match by _id
          }
        }
      },
      {
        $project: { // the only fields i need
          "_id": 1,
          "avatar": 1,
          "name": 1
        }
      }
    ],
    as: "employees" // employees is returned as []
  }
}
]).exec((err, resolve) => {
  if (err) console.log('error', err);
  res.json(resolve);
});

如果它有帮助，这里是我在此聚合中使用的两个集合:

计划架构:

const ScheduleSchema = new Schema({
  store: {
    type: String,
    required: true
  },
  customer: {
    id: {
      type: String
    },
    name: {
      type: String
    },
    avatar: String,
    phone: {
      type: String
    },
    email: { type: String },
    doc: {
      type: String
    },
  },
  employee: {
    id: {
      type: mongoose.Schema.Types.ObjectId,
      required: true
    },
    name: {
      type: String,
      required: true
    },
    avatar: String,
  },
  service: {
    id: {
      type: String
    },
    name: {
      type: String,
      required: true
    },
    filters: [String]
  },
  info: {
    channel: {
      type: String,
      required: true,
      default: 'app'
    },
    id: mongoose.Schema.Types.ObjectId,
    name: String
  },
  scheduleDate: {
    type: String,
    required: true
  },
  scheduleStart: {
    type: String,
    required: true
  },
  scheduleEnd: {
    type: String,
    required: true
  },
  value: {
    type: Number
  },
  comissionType: {
    type: String,
    default: '$'
  },
  comissionValue: {
    type: Number,
    default: 0
  },
  status: {
    type: Number,
    required: true
  },
  observation: String,
  paymentMethod: {
    type: Number,
    default: 0
  },
  paymentValue: String,
  paymentChange: String,
  color: String
}, {
    timestamps: {
      createdAt: 'created',
      updatedAt: 'updated'
    }
  });

员工架构:

const EmployeeSchema = new Schema({
  name: {
    type: String,
    required: true
  },
  a_to_z: String, // nome normalizado, só minusculas e sem espaços
  description: String,
  email: {
    type: String,
    required: true
  },
  avatar: String,
  phone: {
    type: String
  },
  storeOwner: {
    type: Boolean,
    required: true
  },
  permissions: [
    {
      route: String,
      hasPermission: Boolean
    }
  ],
  scheduleAutomatic: {
    type: Boolean,
    required: true,
    default: false
  },
  password: {
    passwordHash: String,
    salt: String
  },
  active: {
    type: Boolean,
    default: true
  },
  storeKey: {
    type: String,
    required: true
  },
  notification_token: String,
  notification_tokens: {
    type: [String],
    default: []
  },
  workingHours: [{
    weekDay: {
      type: Number,
    },
    doesWork: {
      type: Boolean,
    },
    startHour: String,
    endHour: String,
    lunchStart: String,
    lunchEnd: String
  }],
  config: {
    available_days: {
      type: Number,
      default: 365
    },
    in_advance_schedule: {
      type: Number,
      default: 0
    },
    in_advance_interval: {
      type: String,
      default: 'minute'
    }
  }
}, {
  timestamps: {
    createdAt: 'created',
    updatedAt: 'updated'
  } 
});

编辑

我想要达到的结果是这样的:

employees属性是我要使用的属性 $lookup要获得，它将具有与 employee 相同的数据属性，最终它将是一个对象数组，里面只有一个对象。

一些示例文档:

日程:

color: "lavander",
created: "2018-07-31T18:50:53.423Z",
customer: {id: "5b60a67206e8a65f48a15f13", name: "Gabriel Barreto", phone: "11995274098", cpf: "40735255814"},
employee: {id: "5b2952c68424872fccece7f5", name: "Gabriel Barreto", avatar: null},
observation: "teste",
scheduleDate: "2018-08-01",
scheduleEnd: "2018-08-01 08:30",
scheduleStart: "2018-08-01 08:00",
service: {filters: Array(3), id: "5b606e8cc59e82354cc931e2", name: "Corte Masc"},
status: 1,
store: "5b16cceb56a44e2f6cd0324b",
updated: "2018-11-27T13:27:40.310Z",
value: 25,
__v: 0,
_id: "5b60af8de558661acc5d70b9"

员工:

a_to_z: "gabrielbarreto",
active: true,
avatar: "gabriel_barreto_h7qvcn.jpg",
config: {available_days: 180, in_advance_schedule: 10, in_advance_interval: "hour"},
created: "2018-06-19T19:00:22.315Z",
currency: "BRL",
description: "Novo perfil",
email: "gabriel.barreto@wabiz.com.br",
lang: "pt-BR",
name: "Gabriel Barreto",
notification_token: "2d768670-6011-4873-846d-39580b0d82d0",
notification_tokens: ["53049a82-53dc-4bc3-9646-7a4bee1f367b"],
password: null,
permissions: (10) [{…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}],
phone: "11995274098",
scheduleAutomatic: false,
storeKey: "5b16cceb56a44e2f6cd0324b",
storeOwner: true,
token: "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJlbWFpbCI6ImdhYnJpZWwuYmFycmV0b0B3YWJpei5jb20uYnIiLCJpYXQiOjE1NTA2NzEwNDQsImV4cCI6MTU1MzI2MzA0NH0.0Odizd8pS4WPGSqm_2_XrTw1YE8NMOOXnHIrG-WVxGo",
updated: "2019-02-20T13:34:20.619Z",
workingHours: (8) [{…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}],
__v: 0,
_id: "5b2952c68424872fccece7f5"

谢谢你的时间

Answer 1

我在使用 $lookup 和 mongoose 尝试匹配 _id 时也遇到了麻烦，因为我的集合将引用存储为 String 而不是 ObjectId
模型 A:{_id: ObjectId("xxx"), b_id: "eeeee"}
模型 B:{_id: ObjectId("eeeee")}

MyCollectionA.aggregate([
   {
      $lookup: {
        from: "collectionb",
        let: {id: "$b_id"},
        pipeline: [{$match: {$expr: {$eq: ["$_id", "$$id"]}}}],
        as: b
      }
])

在这个例子中，b 永远不会被填充，因为 $$id 不被认为是一个 ObjectId
只需添加一个项目来转换 objectId 中的 $$id 及其工作

MyCollectionA.aggregate([
   {
      $lookup: {
        from: "collectionb",
        let: {id: "$b_id"},
        pipeline: [
           {$project: {_id: 1, bid: {"$toObjectId": "$$id"}}},
           {$match: {$expr: {$eq: ["$_id", "$bid"]}}}
        ],
        as: b
      }
])

或者使用foreignField，localField:

MyCollectionA.aggregate([
   {
      $project:{
        _id: 1,
        b_id: {"$toObjectId": "$b_id"}
      }
   },
   {
      $lookup: {
        from: "collectionb",
        localField: "b_id",
        foreignField: "_id",
        as: b
      }
])