What is the proper way of combining 2 documents in MongoDB-CodePudding

I currently have 2 collections:

users that looks like:

const User = new Schema({
    username:{
        type: String,
        required: true
    },
    password:{
        type: String,
        required: true
    },
    refreshTokens:{
        type: String,
        required: false,
    },
    // ID of the guild a user belongs to
    guildID:{
        type: Schema.Types.ObjectId,
        ref: 'guilds',
        default: '61a679e18d84bff40c2f88fd',
        required: true
    },
    power:{
        type: Number,
        required: true,
        default: 100
    }
})

guilds contains the objectID as _id and a field "name".

Now I would like to get a document by username and also the information of the guild that the user belongs to.

I read about using db.collection.aggregate this however results in all users and their guild information. Is it possible to use $match inside the aggregation to just get that single username? I'm fairly new to MongoDB and am just trying things out. If you have any resources or documentation I'd be happy to read those too!

In SQL it would look something like:

SELECT * FROM users where username = 'SomeUsername' INNER JOIN guilds on users.guildID = guilds.id

CodePudding user response：

Aggregations can solve this (not recommended)

userCollection.aggregate([
  {
    $lookup: {
      from: 'guilds',
      as: 'guild',
      localeField: 'guildID',
      foreignField: '_id',
    }
  },
  {
    $unwrap: {
      path: '$guilds',
      preserveNullAndEmptyArrays: true
  },
  {
    $match: {
      $or: [
        { 'guild._id': guildId },
        { ... other options ... }
      ]
    }
  }
])

While this works and can be reasonably fast depending on your indexes and number of documents it can be better to add frequently queried fields to the related documents. In your case: add guildId and guildName to your user.

While this duplicates data and might not be considered best practice in relational dbs it is common to do this in document based databases. This is the fastest solution.

The alternative to an aggregation and embedding guildData into the user is to send two queries. One for the user, then one for the guild. This is called the relationship-pattern. This is the most common solution I believe)

Many (all?) ODM libraries, such as mongoose, handle the resolving of relationships automatically for you (mongoose calls this population). Which can simplify querying a lot, I think!