CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
torbenrudgaard
torbenrudgaard

Reputation: 2551

MongoDB - rejecting records with $lookup on a value from the $lookup record

I want to get all invoices based on duedates - AND based on the status from the booking table.

I want invoices where booking.status < 5

I do NOT want invoices where booking.status >= 5

This is my aggregate:

db.getCollection('invoice').aggregate([
    {
        $match: {
            dueDate: {$gte: 1483596800},
            dueDate: {$lte: 1583596800}
        }
    },
    {
        $lookup: {
            from: "booking",
            localField: "bookingId",
            foreignField: "_id",    
            as: "booking"       
        }   
    }
])

And here are the tables....

table invoice
{
    "_id" : "IKUU",
    "bookingId" : "AAAAA",
    "dueDate" : 1489470468,
    "invoiceLines" : [ 
        {
            "lineText" : "Rent Price",
            "amountPcs" : "7 x 2071",
            "amountTotal" : 14497
        }, 
        {
            "lineText" : "Discount",
            "amountPcs" : "",
            "amountTotal" : -347
        } 
    ]
}
{
    "_id" : "1NYRO",
    "bookingId" : "BBBBB",
    "dueDate" : 1489471351,
    "invoiceLines" : [ 
        {
            "lineText" : "Reservation / Booking fee",
            "amountPcs" : "1 x 2000",
            "amountTotal" : 2000
        }
    ]
}


table booking
{
    "_id" : "AAAAA",
    "checkin" : 1449756800,
    "price" : 5000,
    "status" : 1
}
{
    "_id" : "BBBBB",
    "checkin" : 1449756800,
    "price" : 6000,
    "status" : 5
}

I tried putting some $match{booking.status: {$lt: 5}} but I cant get it to work.

The result should be the invoice with "bookingId" : "AAAAA".

Upvotes: 0

Views: 94

Answers (1)

AshokGK
AshokGK

Reputation: 875

you need to check for another match for booking status after $lookup like below 

db.getCollection('invoice').aggregate([
    {
        $match: {
            dueDate: {$gte: 1483596800},
            dueDate: {$lte: 1583596800}
        }
    },
    {
        $lookup: {
            from: "booking",
            localField: "bookingId",
            foreignField: "_id",    
            as: "booking"       
        }   
    },
     {
        $match: {
            "booking.status": {$lt: 5},

        }
    }
])

Upvotes: 1

Related Questions