用联接查询& Mongoose中的过滤器

Question

我是Mongodb的新手&在使用平均堆栈构建的web应用程序中使用Mongodb。我的目标是通过加入两个表并对它们应用一个筛选条件来查询它们。我有两个表-自行车-自行车I，注册编号，制造，模型和约会-约会日期，状态，自行车(参考自行车对象)，我只想显示那些没有预约状态=‘Bo对联’的自行车。我想在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
}

Answer 1

您已经快到了，您只需要正确的$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);    
});