Looking for a way to return documents from another collection based on a set from another, MongoDB

Question

To start with, I have two collections, users and sensors. A user looks like:

{
    "_id" : ObjectId("5d471ec414197a868de1b533"),
    "email" : "example_email@hotmail.co.uk",
    "hashedPassword" : "hash",
    "confirmation_code" : "conf",
    "confirmed" : true,
    "sensors" : [
        "asde22re",
    ]
}

And a sensor looks like:

    "_id" : ObjectId("5d4c85d7b032b64f1c1a0b14"),
    "sensorId" : "asde22re",
    "data" : [
        {
            "data" : "10343",
            "time" : "2019-08-08T20:28:07.241Z"
        },
        {
            "data" : "11002",
            "time" : "2019-08-08T20:28:26.594Z"
        }
    ]

I have an endpoint GET /sensors, which is supposed to return data for all sensors of which a user has access too, this can be found in users.sensors.

I'm unsure how to actually google what I want (probably ignorance on my part), but essentially I need a query where given users.email it returns all the sensors that the respective user has access too.

One way I could do this is just by doing:

dbo.collection('users').findOne({ email: 'example_email' }) and then using this response, get users.sensors and do a foreach on each sensor in the set, this seems inefficient though.

How can I combine that into one query?

Edit: If it helps, this is what I am trying to replicate

dbo.collection('users').findOne({ email }, { fields: { sensors: 1, _id: 0 } }, (err, results) => {

    if(err){
        return res.json({ err });
    }

    const { sensors } = results;

    dbo.collection('sensors').find({ sensorId: { $in: [sensors] } }).toArray((err, results) => {

        if(err){
            return res.json({ err });
        }

        return res.json({ results });

    });

});

Answer 1

Please try this :

dbo.collection('users').aggregate([{ $match: { email: 'example_email@hotmail.co.uk' } }, { $unwind: "$sensors" },
{
    $lookup:
    {
        from: "sensors",
        localField: "sensors",
        foreignField: "sensorId",
        as: "sensorDetails"
    }
}, {
    $group: { _id: '$_id', "userDetails": { "$first": "$$ROOT" }, "sensors": { "$push": "$sensors" }, "sensorDetails": { "$push": { "$arrayElemAt": ["$sensorDetails", 0] } } }
},
{ $addFields: { 'userDetails.sensors': '$sensors', 'userDetails.sensorDetails': '$sensorDetails' } },
{
    $replaceRoot: { "newRoot": "$userDetails" }

}])

With mongoDB v3.4 or above you can just use $lookup to achieve expected results, here you don't need to use $unwind on localField even if it's an array, it doesn't matter anymore :

dbo.collection('users').aggregate([{ $match: { email: 'example_email@hotmail.co.uk' } },
{
    $lookup:
    {
        from: "sensors",
        localField: "sensors",
        foreignField: "sensorId",
        as: "sensorDetails"
    }
}])

Answer 2

Similar to "joins" in sql, in Mongo you have to use "lookup".

To use lookup you have to use "aggregate" query,

So query that you need is,

db.collection('users').aggregate({$match:{ email: 'example_email' }},
    {$unwind:{path:"$sensors"}},
    {$lookup:{from:"sensor", localField: "sensors", foreignField:"sensorId", as:"sensorDetails"}},
(err, userData)=>{
      console.log(userData);
})

So what this query is doing,

see "$lookup" line ---> from "users" collection, it is using "sensors" field (local field to your user collection like the primary id in sql) and fetch info from "sensor" collection matching to sensorId (foreign field in sensor collection), and store the result in "sensorDetails" field.

you can have access to sensorDetails with "userData[0].sensorDetails". sensorDetails will be array.

Check official docs about lookup Also read about unwind