How to do inner joining in MongoDB?
Asked Answered
O

8

31

Is it possible to do SQL inner joins kind of stuff in MongoDB?

I know there is the $lookup attribute in an aggregation pipeline and it is equivalent to outer joins in SQL, but I want to do something similar to inner joins.

I have three collections which need to merge together:

// User Collection
db.User.find({});

// Output:
{
   ID : 1,
   USER_NAME : "John",
   password : "pass"
}
{

   ID : 2,
   USER_NAME : "Andrew",
   PASSWORD : "andrew"
}

// Role Collection
db.ROLE.find({});

// Output:
{
   ID : 1,
   ROLE_NAME : "admin"
},
{
    ID : 2,
    ROLE_NAME : "staff"
}

// USER_ROLE Collection
db.USER_ROLE.find({});

// Output:
{
   ID : 1,
   USER_ID : 1,
   ROLE_ID : 1
}

I have the above collections and I want to extract only the documents matched with users and their respective roles, not all the documents. How can I manage it in MongoDB?

Osteoblast answered 1/6, 2016 at 17:57 Comment(3)
If inner joins are critical, consider embedding data. Otherwise, you have to run multiple queries to join multiple collections exactly the way you want.Falcate
convert any query to mongo query: #68156215Bloomsbury
MongoDB sucks for lack of inner join. If it's supposed to handle large amounts of data, then a M:N relationship is pretty common. Even though you can embedded data, it's not ideal if you have a single M:N relationship which you will in most projects. SQL is only a good choice because of thisHernandez
H
23

As Tiramisu wrote this looks like schema issue.

You can make a manual inner join, by removing documents where $lookup returned empty array.

....
{$lookup... as myArray},
{$match: {"myArray":{$ne:[]}}},
{$lookup... as myArray2},
{$match: {"myArray2":{$ne:[]}}},

schema change

I personally will go for schema update, like this:

db.User.find({})
{
   ID : 1,
   USER_NAME : "John",
   password : "pass"
   roles:[{ID : 1,  ROLE_NAME : "admin"}]
}


db.ROLE.find({})
{
   ID : 1,
   ROLE_NAME : "admin"
},
Handbill answered 1/6, 2016 at 22:16 Comment(2)
thanks for the answer profesor79 but is it a good way to map embed way isn't that cause data redundancy assume if many users has same role then same role will repeatedly added in all the documents thats the problem i havingOsteoblast
If you are worried about data redundancy go for relational DB. NoSql approach favors denormalised data.Ambivalence
O
28

I found answer my self it was

$unwind done the trick to me following query worked for me

    db.USER.aggregate([{
            $lookup: {
                from: "USER_ROLE",
                localField: "ID",
                foreignField: "USER_ID",
                as: "userRole"
            }
        }, {
            $unwind: {
                path: "$userRole",
                preserveNullAndEmptyArrays: false
            }
        }, {
            $lookup: {
                from: "ROLE",
                localField: "userRole.ROLE_ID",
                foreignField: "ID",
                as: "role"
            }
        }, {
            $unwind: {
                path: "$role",
                preserveNullAndEmptyArrays: false
            }
        }, {
            $match: {
                "role.ROLE_NAME": "staff"
            }, {
                $project: {
                    USER_NAME: 1,
                    _id: 0
                }
            }
            ]).pretty()

Anyway thanks for the answers

Osteoblast answered 2/6, 2016 at 6:27 Comment(1)
Is there any difference between this and $match: { $ne } method? Which one is more "idiomatic"?Nervine
H
23

As Tiramisu wrote this looks like schema issue.

You can make a manual inner join, by removing documents where $lookup returned empty array.

....
{$lookup... as myArray},
{$match: {"myArray":{$ne:[]}}},
{$lookup... as myArray2},
{$match: {"myArray2":{$ne:[]}}},

schema change

I personally will go for schema update, like this:

db.User.find({})
{
   ID : 1,
   USER_NAME : "John",
   password : "pass"
   roles:[{ID : 1,  ROLE_NAME : "admin"}]
}


db.ROLE.find({})
{
   ID : 1,
   ROLE_NAME : "admin"
},
Handbill answered 1/6, 2016 at 22:16 Comment(2)
thanks for the answer profesor79 but is it a good way to map embed way isn't that cause data redundancy assume if many users has same role then same role will repeatedly added in all the documents thats the problem i havingOsteoblast
If you are worried about data redundancy go for relational DB. NoSql approach favors denormalised data.Ambivalence
G
2

Will this help

const RolesSchema = new Schema({
  ....

});
const Roles = mongoose.model('Roles', RolesSchema);


const UserSchema = new Schema({
  ...

  roles: [{ type: mongoose.Schema.Types.ObjectId, ref: "Roles" }]
});

using the populate on userschema you can also reduce the redundancy

Granvillegranvillebarker answered 5/2, 2020 at 19:26 Comment(0)
P
2

Well you are correct, $lookup attribute is equivalent to outer joins in SQL, but in mongoDB, you need additional aggregation stages so as to perform a similar INNER JOIN in mongo. Here's an example for joining User and ROLE collections based on the ID and displaying the results based on USER_NAME and ROLE_NAME

    db.User.aggregate([{
      $lookup: {
          from: "ROLE",
          localField: "ID",
          foreignField: "ID",
          as: "result"
        }
    },{
       $match: {
           result: {
             $ne: []
           }
        }
    },{
      $addFields: {
           result: {
             $arrayElemAt: ["$result",0]
           }
      }
    },{
       $project: {
          USER_NAME: "$USER_NAME",
          ROLE_NAME: "$result.ROLE_NAME"
       }
    }])

Hope it helps!!

Porterhouse answered 17/11, 2022 at 22:39 Comment(0)
P
0

MongoDB $lookup aggregation is the most formal and the best-optimized method for this question. However, If you are using Node.js as the server-side, Then you can use a little hack as follows.

CollectionOne.find().then((data0) => {
    if (data0.length > 0) {
        let array = [];
        for (let i = 0; i < data0.length; i++) {
            let x = data0[i]
            let y = x.yourForeignKey;
            array.push({_id: y});
        }
        CollectionTwo.find(
            {$or: array}
        ).then((data1) => {
            res.status(200).json(data1);
        }).catch((error1) => {
            return error1;
        })
    }
}).catch((error0) => {
    return error0;
});

I used the Array Push() method and the $or operator of MongoDB. You can use the $nor operator instead of the $or to find outer join documents. And also you can change the finding algorithm by using $ne, $nor, $or, $and and etc.

Propagandism answered 24/10, 2021 at 4:53 Comment(0)
T
0

$lookup aggregation
Performs a left outer join to a collection in the same database to filter in documents from the "joined" collection for processing. The $lookup stage adds a new array field to each input document. The new array field contains the matching documents from the "joined" collection. The $lookup stage passes these reshaped documents to the next stage.

Starting in MongoDB 5.1, $lookup works across sharded collections.

To combine elements from two different collections, use the $unionWith pipeline stage.

Syntax
The $lookup stage has the following syntaxes:

Equality Match with a Single Join Condition
To perform an equality match between a field from the input documents with a field from the documents of the "joined" collection, the $lookup stage has this syntax:

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

More details: https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup/

Thorwald answered 23/9, 2022 at 4:9 Comment(0)
C
0

Yes you can do using lookup. Check out syntaxfactory.io for example code. Select mongodb and search.It will show examples for all types of joins you can do in mongodb

Clive answered 16/1, 2024 at 18:54 Comment(0)
F
-5
> show dbs
admin   0.000GB
config  0.000GB
local   0.002GB
> use local
switched to db local
> show collections
startup_log
test1
test2
> db.test2.aggregate([{
...    $lookup: {
...       from: "test1",
...       localField: "id",
...       foreignField: "id",
...       as: "aggTest"
...    }
... }])
Facient answered 5/2, 2020 at 7:31 Comment(1)
This doesn't provide any useful informationAbridgment

© 2022 - 2025 — McMap. All rights reserved.