1

I'm having a bit of an issue here, as I'm trying to wrap my head around the usage of $lookup when doing a query through multiple collections, as explained here: https://stackoverflow.com/a/43653679 and here: https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/

Got 3 collections:

Users - where can use 'email' field

Config - where can use 'vpn' field

Firmware - where can use 'version' field

My intent is to count the number of users that match these conditions:

  • email =! @yahoo.com,@gmail.com,@hotmail.com
  • vpn is ON
  • version is 123

Can you please help me understand how my query should look? Thanks a lot!

Erik
  • 23
  • 6

2 Answers2

3

I'm not at my workstation at the moment, but I believe this is what you're looking for.

The links you have addressed above in your question explain perfectly what you're looking to do Just seems you're having difficulty working it out in your head (Which we all do.. I know do a lot)

For future reference this would normally be considered/flagged as a duplicate of the question you referenced but I see you're new here and I'm in a great mood for some reason. Hope this helps and if not let me know! Welcome to the community!

db.Users.aggregate([
    { $match: { email: "test@somemail.com" } }, #Get the users that match the email constraints
    {
        $lookup: { #Get the config document associated to the/each user
            from: "Config",
            localField: "config_id",
            foreignField: "_id",
            as: "config"
        }
    },
    {
        $match: { #limit the configs with VPNs that are "ON"
            "config.vpn": "ON"
        }
    },
    {
        $lookup: { #Get the Firmware associated to the/each User
            from: "Firmware",
            localField: "firmware_id",
            foreignField: "_id",
            as: "firmware"
        }
    },
    {   
        $match: { #Limit to only firmwares that are version 123
               "firmware.version": 123
        }
    },
    $count: { "_id" }
])

This (in theory) would return a document like this:

 { "_id": <the number of Users with specified "email", "vpn", "version"> }
Jab
  • 26,853
  • 21
  • 75
  • 114
  • Hi Jaba. Thanks a lot for the kind answer! As it turns out, I can't really use this approach as I actually have 2 databases: Devices DB > Config collection > vpn field + Devices DB > Firmware collection > version field and Users DB > User collection > email field What binds these collections together is a field called tenantId, present in all of them. I'm not sure what yo mean by config_id (_id in general), because based on the examples from https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/ I would've said that instead config_id, I were to put vpn and so on. – Erik Oct 13 '18 at 14:12
  • Are you attempting to do this right in the mongo shell, or are you writing this in js or python or such? Also, I only used those custom `_id`'s to make it known I was referring to the `customers_id` field. this would be equivalent to your tenant_id – Jab Oct 14 '18 at 04:47
  • Using a mongo shell. I guess I was doing it wrong, right? – Erik Oct 15 '18 at 11:15
  • Well I’d suggest moving your databases together and separating them by collections, this way you could use the logic I did above. If not there is no way to do all of this in one command – Jab Oct 15 '18 at 19:54
1

I ended up using a js script. Here's most of it:

let vpnConfigs = dbRefs.Devices.VpnConfig.find({"vpn": "on"}).toArray() 
let firmwareConfigs = dbRefs.Devices.FirmwareConfig.find({"version": "1.2.1"}).toArray()

let tenants = dbRefs.Users.Tenant.find().toArray()

let filteredDevices = vpnConfigs.filter(   vpnConfigModel => firmwareConfigs.some(firmwareConfigModel => vpnConfigModel.parent.id
=== firmwareConfigModel.parent.id) )

let totalDevicesNumber = 0

let filteredTenants = tenants.filter(
    tenant => {
        if(/@psft\.com|@gmail\.com|@yahoo\.com|@maildrop.cc/.test(tenant.ownerEmail)){
            return false;
        }

        return filteredDevices.some(configModel => configModel.tenantId === tenant._id)
        let devicesCount = filteredDevices.filter(configModel => configModel.tenantId === tenant._id).length
        totalDevicesNumber += devicesCount
        return devicesCount > 0
    } )

filteredTenants.map(
    tenant => print(tenant.ownerEmail) )


print(`Found ${filteredTenants.length} tenant(s)`) 
print(`Found ${totalDevicesNumber} device(s)`)
Erik
  • 23
  • 6