MongoDB join two collections with between clause

Question

There is a collection "Printers":

{ 
"_id" : ObjectId("5cc02f9b9931de72296ba6c2"), 
"model" : "Xerox WorkCentre 3315", 
"serial" : "3255498494", 
"date" : ISODate("2019-04-25T08:57:48.001+0000"), 
"pages" : NumberInt(4868), 
"location" : "New location", 
"ip" : "10.159.0.35", 
"ip_int" : NumberInt(178192419)
}

and "Branches" collection:

{ 
 "_id" : ObjectId("5cb4799b8c0cfe35e4a4c266"), 
 "name" : "Office 1", 
 "ip_start" : NumberLong(178192384), 
 "ip_end" : NumberLong(178194431)
}
 // ----------------------------------------------
{ 
 "_id" : ObjectId("5cb479e68c0cfe35e4a4c269"), 
 "name" : "Office 2", 
 "ip_start" : NumberLong(3232258048), 
 "ip_end" : NumberLong(3232258303)
}

"Branches" collection contains ip addresses converted into integer value, i.e. 192.168.0.1 is 3232235521. Each record in Branches describes subnet. Each printer located in one branch.

If printers.ip_int between branches record [ip_start;ip_end] then query should return all fields from Printer and one field "Name" from "Branches" collection.

How can i do this?

Answer 1

db.getCollection("printers").aggregate(
[
    { 
        "$lookup" : {
            "from" : "branches", 
            "let" : {
                "ip_int" : "$ip_int"
            }, 
            "pipeline" : [
                {
                    "$match" : {
                        "$expr" : {
                            "$and" : [{"$gte" : ["$$ip_int", "$ip_start"]}, 
                                { "$lte" : ["$$ip_int", "$ip_end"]}
                            ]
                        }
                    }
                }
            ], "as" : "Printers"
        }
    }, 
    { 
        "$sort" : {
            "ip_int" : 1.0
        }
    }, 
    { 
        "$unwind" : {
            "path" : "$Printers"
        }
    }, 
    { 
        "$addFields" : {
            "filial" : "$Printers.name"
        }
    }, 
    { 
        "$project" : {
            "Printers" : false, "ip_int" : false
        }
    }
]);

Answer 2

You need a lookup with custom pipeline where you can specify "between" condition:

db.Branches.aggregate([
    {
        $lookup: {
            from: "Printers",
            let: { ip_start: "$ip_start", ip_end: "$ip_end" },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $and: [
                                { "$gte": [ "$ip_int", "$$ip_start" ] },
                                { "$lte": [ "$ip_int", "$$ip_end" ] },
                            ]
                        }
                    }
                }
            ],
            as: "Printers"
        }
    }
])