在 Mongoose 中使用连接和过滤器进行查询

我是 Mongodb 的新手，并且在我使用 MEAN 堆栈构建的 Web 应用程序中使用它。我的目标是通过连接两个表并对它们应用过滤条件来查询它们。例如：我有两个表 - 自行车-自行车 ID、注册号、品牌、型号和预约 - 预约日期、状态、自行车（参考自行车对象），我只想显示那些没有预约状态=的自行车“已预订”。我想在 Mongoose 中完成以下 SQL。

Select bike.* from Bike inner join Appointment on Bike.BikeID = Appointment.BikeID and Appointment.Status != 'Booked'

我正在使用以下代码，但没有得到所需的结果。有人可以帮我解决这个问题吗？

app.get('/api/getbikeappo*',function(req,res){
        var msg="";
        //.find({cust:req.query._id})
        //.populate('cust','email')
        ubBike.aggregate([
                {
                    $match:
                    {
                        cust : req.query._id
                    }
                },
                {
                    $lookup:
                    {
                        from: "appos",
                        localField: "_id",
                        foreignField: "bike",
                        as : "appointments"
                    }
                },
                {
                    $match:
                    {
                        "appointments" : {$eq : []}
                    }
                }
            ])
            .exec(function(err,bikes){
                 res.send(bikes);
                if(err) throw err;
            });
    }); 

bikes - collection
{
    "_id": {
        "$oid": "57fb600fdd9070681de19c18"
    },
    "brand": "Splendor",
    "model": "Splendor",
    "year": "2002",
    "kms": 0,
    "regno": "TN02M8937",
    "cust": {
        "$oid": "57f8c44466cab97c1355a09a"
    },
    "__v": 0
}
{
    "_id": {
        "$oid": "57fb6025dd9070681de19c19"
    },
    "brand": "Activa",
    "model": "Activa",
    "year": "2016",
    "kms": 0,
    "regno": "TN14M3844",
    "cust": {
        "$oid": "57f8c44466cab97c1355a09a"
    },
    "__v": 0
}

appointment collection
----------------------
{
    "_id": {
        "$oid": "57fb6040dd9070681de19c1a"
    },
    "appoidt": {
        "$date": "2016-10-15T18:30:00.000Z"
    },
    "reqdt": {
        "$date": "2016-10-10T09:32:48.694Z"
    },
    "status": "Booked",
    "bike": {
        "$oid": "57fb600fdd9070681de19c18"
    },
    "cust": {
        "$oid": "57f8c44466cab97c1355a09a"
    },
    "__v": 0
}
-----------------
Expected output is 

{
    "_id": {
        "$oid": "57fb6025dd9070681de19c19"
    },
    "brand": "Activa",
    "model": "Activa",
    "year": "2016",
    "kms": 0,
    "regno": "TN14M3844",
    "cust": {
        "$oid": "57f8c44466cab97c1355a09a"
    },
    "__v": 0
}

你就快到了，你只需要正确的$match https://docs.mongodb.com/manual/reference/operator/aggregation/match/查询如下：

ubBike.aggregate([
    { "$match": { "cust": req.query._id } },
    {
        "$lookup": {
            "from": "appos",
            "localField": "_id",
            "foreignField": "bike",
            "as": "appointments"
        }
    },
    { "$match": { "appointments.status": { "$ne": "Booked" } } }
]).exec(function(err, bikes){
    if(err) throw err;
    res.send(bikes);    
});