Querying with joins & filters in Mongoose

Question

I'm new to Mongodb & using it in web application that I'm building using MEAN stack. My goal is to query two tables by joining them and applying a filter condition on them. For eg: I have two tables - Bike-BikeID,Registration No.,Make,Model & Appointment - Appointment Date,Status,Bike(ref Bike object) and I want to show only those bikes who do not have an appointment with status='Booked'. I want to accomplish the following SQL in Mongoose.

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

I'm using the following code, but I'm not getting the desired results. Can someone help me with this query.

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

You were almost there, you just needed the right $match query which follows:

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);    
});