How to join 2 tables or more

Working with Data Node.js Frameworks
,
1

Hello everyone.

I have two collections, one is for the user data, with firstName, lastName, etc and theres a field called userRole:

        userRole:{
            type: mongoose.Schema.ObjectId,
            ref: 'userRoles',
            required: [true, 'Please Select a Role']
        }

This field will be responsible to save the role id, it’s a join field.

The question is, how may I join the collection users with the collection usersRole, and also I would like to show all the fields for the user Collection but bring from the usersRole only the roleDescription, actually is a user level query, I’m trying to show the user info. inside a table and I need the user name, user address and the user Role.

I would like to thank everyone for the help.

Best Regards,
Ricardo Scarpim

1 Like
2

Hi @ricardo_scarpim - Welcome to the community :wave:

Sounds like $lookup might suit your use case. However, do you have some sample documents from both collections you could provide here? Please also advise the expected output / what you are attempting to “join” on based off the sample documents.

Regards,
Jason

2 Likes
4

And here are the mongoose schema for users and usersroles:

onst mongoose      = require('mongoose')
const bcrypt        = require('bcryptjs')
const jwt           = require('jsonwebtoken')

const userSchema = mongoose.Schema(
    {
        firstName: {
            type: String,
        },
        lastName: {
            type: String,
        },
        emailAddress: {
            type: String,
        },
        phoneNumber: {
            type: String,
        },
        country: {
            type: String,
        },
        streetAddress: {
            type: String,
        },
        city: {
            type: String,
        },
        region: {
            type: String,
        },
        postalCode: {
            type: String,
        },
        userName: {
            type: String,
            required: [true, 'Please add a name.'],
        },
        userPassword: {
            type: String,
            required: [true, 'Please add a password.'],
        },
        userRole:{
            type: mongoose.Schema.ObjectId,
            ref: 'userRoles',
            required: [true, 'Please Select a Role']
        }
    },
    {
        timestamps: true,
    }
)

/** Encrypting the Password. */
userSchema.pre('save', async function(next){
    
    const salt          = await bcrypt.genSalt(10)

    this.userPassword   = await bcrypt.hash(this.userPassword, salt)
})

/** Sign JWT and Return */
userSchema.methods.getSignedJwtToken = function(){
    return jwt.sign({ id: this._id}, process.env.JWT_SECRET, {
        expiresIn: process.env.JWT_EXPIRE
    })
}

module.exports = mongoose.model('userMD', userSchema, 'Users');


const mongoose = require('mongoose')

const userRolesSchema = mongoose.Schema(
    {

        roleDescription: {
            type: String,
            required:[true, 'Please Add a Valid User Role']
        }
    },
    {
        timestamps: true
    }
)

module.exports = mongoose.model('userRoles', userRolesSchema, 'UserRoles')
5

The output I’m getting is the following:

city: "Hillside"
country: ""
createdAt: "2022-09-13T21:13:37.173Z"
emailAddress: "aa@hotmail.com"
firstName: "maria"
lastName: ""
phoneNumber: ""
postalCode: ""
region: ""
roleDescription: []
streetAddress: "
updatedAt: "2022-09-13T21:13:37.173Z"
userName: "maria@hotmail.com"
userPassword: "$2a$10$8Nz9HWmh8x6aO8vYVtu80u.M3nEqH.iXvW9TBuK.PBfmi8IlIVsf2"
userRole: "6320af5955d957de42c77319"
__v: 0
_id: "6320f2817618d1af1b17fe65"
6

The method that I used to call the $lookup is the following:

    const Users = await User.aggregate([
        {
            $lookup: {
                from: "userRoles",
                pipeline: [
                    { $project: {roleDescription: 1}}
                ],
                as: "roleDescription"
            }
        }
    ])

Thank you for your help.

7

Hi @ricardo_scarpim,

const Users = await User.aggregate([
        {
            $lookup: {
                from: "userRoles",
                pipeline: [
                    { $project: {roleDescription: 1}}
                ],
                as: "roleDescription"
            }
        }
    ])

I’m not too familiar with mongoose but would it be possible the collection name is userroles instead of userRoles (this is based off my interpretation from the following documentation)? Can you try verify this by connecting to the deployment via mongosh, switching to the database where the two collections in question exist and then running the following:

show collections

Please advise the output from the above.

Example output:

DB>show collections
userRoles
users

Regards,
Jason

1 Like
8

Hey Jason, once again thank you so much for the help, still not working lol, here’s the output

city: "bla bla"
country: ""
createdAt: "2022-09-15T20:49:13.325Z"
emailAddress: "aaa@hotmail.com"
firstName: "bla"
lastName: "bla"
phoneNumber: ""
postalCode: "00000"
region: "NN"
roleDescription: Array(0)
length: 0
[[Prototype]]: Array(0)
streetAddress: "bla bla"
updatedAt: "2022-09-15T20:49:13.325Z"
userName: "aa@hotmail.com"
userPassword: "$2a$10$YzmjTXnYhaDnJXV5H4G0dO3ybl7tajaWaYet9PPksUBb4A2YaBSGC"
userRole: "630d94884b41135bfa488036"
__v: 0
_id: "63238fc9ef26265f5d65cb9d"
1 Like
9

Thanks for confirming. Can you advise the output from show collections from mongosh as advised in my previous reply?

If connected via mongosh, I would also try the pipeline you wanted to see if the documents return in shell.

Regards,
Jason

1 Like