CODEWITHSUNDEEP
mongodb
torbenrudgaard
torbenrudgaard

Reputation: 2551

Triple relation lookup in MongoDB

I have tried to solve this one but its WAY over my Mongo skill level. I hope there are some hardcore Mongo wizards who have an idea :-)

I would like to make a result where 

db.getCollection('invoice').find({
        dueDate: {
            $gte:148000000,
            $lt: 149000000
        }        
})

This is the "invoice" table....

invoice
{
    "_id" : "KLKIU",
    "invoiceNumber" : 1,
    "bookingId" : "0J0DR",
    "dueDate" : "148100000",
    "account" : "aaaaaaaaaa",
    "invoiceLines" : [ 
        {
            "lineText" : "Booking fee",
            "amount" : 1000
        }, 
        {
            "lineText" : "Discount",
            "amount" : -200
        }, 
        {
            "lineText" : "Whatever extra",
            "amount" : 400
        }
    ]
}

this is the result

{
    "_id" : "KLKIU",
    "invoiceNumber" : 1,
    "bookingId" : "0J0DR",
    "dueDate" : "148100000",
    "account" : "aaaaaaaaaa",
    "invoiceLines" : [ 
        {
            "lineText" : "Booking fee",
            "amount" : 1000
        }, 
        {
            "lineText" : "Discount",
            "amount" : -200
        }, 
        {
            "lineText" : "Whatever extra",
            "amount" : 400
        }
    ], 
    "propertyName" : "Atlantis Condo",
}

please notice the "propertyName" at the bottom

it needs to lookup and add "propertyName" : "Atlantis Condo", which will be done like this

db.getCollection('booking').find({
    booking._id: invoice.bookingId
})

and then 

db.getCollection('property').find({
    property._id: booking:propertyId 
})

These are the two tables:

Booking
{
    "_id" : "0J0DR",
    "propertyId" : "58669471869659d70b424ea7",
}

Property
{
    "_id" : "58669471869659d70b424ea7",
    "propertyName" : "Atlantis Condo",
}

Hope someone can figure this out - right now im doing some horrible sequential loops, and with big amounts of data thats really slow.

Upvotes: 0

Views: 180

Answers (1)

s7vr
s7vr

Reputation: 75934

You can try below aggregation.

$lookup's to join to Booking and Property collection.

$unwind to flatten the booking array output from $lookup for joining on local field to Property collection.

$addFields to project the propertyName field.

$project to exclude the fields from referenced collection.

db.getCollection('invoice').aggregate([{
    $match: {
        "dueDate": {
            $gte: 148000000,
            $lt: 149000000
        }
    }
}, {
    $lookup: {
        from: "Booking",
        localField: "bookingId",
        foreignField: "_id",
        as: "booking"
    }
}, {
    $unwind: "$booking"
}, {
    $lookup: {
        from: "Property",
        localField: "booking.propertyId",
        foreignField: "_id",
        as: "property"
    }
}, {
    $unwind: "$property"
}, {
    $addFields: {
        "propertyName": "$property.propertyName"
    }
}, {
    $project: {
        "booking": 0
    }
}, {
    $project: {
        "property": 0
    }
}])

Upvotes: 1

Related Questions