Many to many relationship with extra parameters in MongoDB-CodePudding

I building a management system for a window cleaning company with multiple services. I have two collections: Customers and Services. Each customer can have multiple services and each services can belong to multiple customers.

From what I know this is a basic many to many relationship and should be something like this

Customers

{
    _id: 1,
    name: 'Customer 1',
    services: [101, 102]
}
{
    _id: 2,
    name: 'Customer 2',
    services: [101]
}

Services

{
    _id: 101,
    name: 'Service 1',
    customers: [1, 2]
}
{
    _id: 102,
    name: 'Service 2',
    customers: [1]
}

So far so good. I can query for a customer and get all the services and from a service I can get the customers.

But I need more data. For each relationship I want a start date, end date (optional), a frequency, etc.

In a SQL environment with a relational database I made a many-to-many relationship table with two foreign keys and extra columns for the additional data. Has MongoDB something to easily handle this?

CodePudding user response：

I think a many-to-many relationship table with two foreign keys and extra columns for the additional data is also recommended for MongoDB.

Check out my mongoplayground below.

db.Customers.aggregate([
  {
    $match: {
      _id: 1
    }
  },
  {
    $lookup: {
      from: "Relationships",
      localField: "_id",
      foreignField: "Customer_id",
      as: "docs",
      pipeline: [
        {
          $lookup: {
            from: "Services",
            localField: "Service_id",
            foreignField: "_id",
            as: "docs",
            pipeline: []
          }
        },
        {
          $set: {
            docs: {
              $first: "$docs.name"
            }
          }
        }
      ]
    }
  }
])

mongoplayground