I have an orders collection where each order has the following shape:

  {
    "_id": "5252875356f64d6d28000001",
    "lineItems": [
      { productId: 'prod_007', quantity: 3 }, 
      { productId: 'prod_003', quantity: 2 }
    ]
    // other fields omitted
  }

I also have a products collection, where each product contains a unique productId field.

How can I populate each lineItem.productId with a matching product from the products collection? Thanks! :)

EDIT: orderSchema and productSchema:

const orderSchema = new Schema({
  checkoutId: {
    type: String,
    required: true,
  },
  customerId: {
    type: String,
    required: true,
  },
  lineItems: {
    type: [itemSubSchema],
    required: true,
  },
});

const itemSubSchema = new Schema(
  {
    productId: {
      type: String,
      required: true,
    },
    quantity: {
      type: Number,
      required: true,
    },
  },
  { _id: false }
);

const productSchema = new Schema({
  productId: {
    type: String,
    required: true,
  },
  name: {
    type: String,
    required: true,
  },
  imageURL: {
    type: String,
    required: true,
  },
  price: {
    type: Number,
    default: 0, 
  },
});

CodePudding user response：

I don't know the exact output you want but I think this is what you are looking for:

The trick here is to use $lookup in an aggregation stage.

• First $unwind to deconstruct the array and can merge each id with the other collection.
• Then the $lookup itself. This is like a join in SQL. It merges the desired objects with same ids.
• Then recreate the population using $mergeObjects to get properties from both collections.
• And last re-group objects to get the array again.

db.orders.aggregate([
  {
    "$unwind": "$lineItems"
  },
  {
    "$lookup": {
      "from": "products",
      "localField": "lineItems.productId",
      "foreignField": "_id",
      "as": "result"
    }
  },
  {
    "$set": {
      "lineItems": {
        "$mergeObjects": [
          "$lineItems",
          {
            "$first": "$result"
          }
        ]
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "lineItems": {
        "$push": "$lineItems"
      }
    }
  }
])

Example here

With this query you have the same intial data but "filled" with the values from the other collection.

Edit: You can also avoid one stage, maybe it is clear with the $set stage but this example do the same as it merge the objects in the $group stage while pushing to the array.

CodePudding user response：

You can use the Mongoose populate method either when you query your documents or as middleware. However, Mongoose only allows normal population on the _id field.

const itemSubSchema = new Schema({
  product: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'productSchema',
  }
});

const order = await orderSchema.find().populate('lineItems.$*.product');
// special populate syntax necessary for nested documents

Using middleware you would still need to reconfigure your item schema to save the _id from products. But this method would automatically call populate each time you query items:

itemSubSchema.pre('find', function(){
  this.populate('product');
});

You could also declare your item schema within your order schema to reduce one layer of joining data:

const orderSchema = new Schema({
 lineItems: [{
  type: {
   quantity: {type: Number, required: true},
   product: {
    type: mongoose.Schema.Types.ObjectId,
    required: true,
    ref: 'productSchema',   
   }
  },
  required: true,
 }]
});

const orders = orderSchema.find().populate('lineItems');