Let's say we have two collections: Order and Seller for an Ebay like business where customers can order items from individual sellers.

Each Order contains a seller field which lists the ID of the shop owner.

const orderSchema = new mongoose.Schema({
  seller: { type: mongoose.Schema.Types.ObjectId, required: true, ref: 'Seller' },
  item: { type: String },
});

const Order = mongoose.model('Order', orderSchema);

When I try to use $lookup

  const aggregateOrdersWithSellerInfo = await Order.aggregate([
    {
      $lookup: {
        from: 'Seller',
        localField: 'seller',
        foreignField: '_id',
        as: 'seller_info',
      },
    },
  ]).exec();

all seller_info fields (ex: aggregateOrdersWithSellerInfo[0].seller_info) return an empty array:

> (0) []

But I would expect it to return the seller associated with the seller field on each Order such as:

// Seller doc
  {
    _id: ObjectId("62aa38d68e006f3006efe520"),
    firstName: 'Nikki',
    __v: 0
 }

Here's an example of the Order doc

 {
    _id: ObjectId("62aa38d68e006f3006efe522"),
    seller: ObjectId("62aa38d68e006f3006efe520"),
    item: 'Mango Body Butter',
    __v: 0
  }

How to get the associated seller document using aggregation?

Full Code

const mongoose = require('mongoose');

const connect = async (dsn) =>
  mongoose.connect(dsn, {
    useNewUrlParser: true,
    useUnifiedTopology: true,
  });

// Order Schema

const orderSchema = new mongoose.Schema({
  seller: { type: mongoose.Schema.Types.ObjectId, required: true, ref: 'Seller' },
  item: { type: String },
});

const Order = mongoose.model('Order', orderSchema);

// Seller Schema
const sellerSchema = new mongoose.Schema({
  firstName: { type: String },
});

const Seller = mongoose.model('Seller', sellerSchema);

// Seeder
const seedLocalDatabase = async () => {
  await connect('mongodb://127.0.0.1:27017/fakewishtender');
  await Seller.deleteMany({});

  const sellers = [
    {
      firstName: 'Nikki',
    },
    {
      firstName: 'Alice',
    },
  ];

  const sellersInsertedRes = await Seller.insertMany(sellers);

  await Order.deleteMany({});
  const orders = [
    {
      seller: sellersInsertedRes.find((seller) => seller.firstName === 'Nikki')._id,
      item: 'Mango Body Butter',
    },
    {
      seller: sellersInsertedRes.find((seller) => seller.firstName === 'Alice')._id,
      item: 'Vintage Jacket',
    },
  ];

  await Order.insertMany(orders);
};

// Aggregation
(async () => {
  await seedLocalDatabase();

  const aggregateOrdersWithSellerInfo = await Order.aggregate([
    {
      $lookup: {
        from: 'Seller',
        localField: 'seller',
        foreignField: '_id',
        as: 'seller_info',
      },
    },
  ]).exec();

  const allSellers = await Seller.find({});
  const allOrders = await Order.find({});

  const sellersWithOrders = allOrders.map((order) =>
    allSellers.filter((seller) => seller._id.toJSON() === order.seller.toJSON())
  );
  const sellersPopulatedWithAggregate = aggregateOrdersWithSellerInfo.map(
    (order) => order.seller_info
  );

  console.log(
    `
    
    Sellers populated with aggregation are: 
    
    ${JSON.stringify(sellersPopulatedWithAggregate)}
    
    `
  );
  console.log(
    `But I would expect sellers populated with aggregation to be: 
    
    ${JSON.stringify(sellersWithOrders)}
    
    `
  );
  mongoose.disconnect();
})();

CodePudding user response：

You need to change the values of localField and foreign field:

const aggregateOrdersWithSellerInfo = 
  await Order.aggregate([
    {
      $lookup: {
        from: 'Seller',
        localField: 'seller',
        foreignField: '_id',
        as: 'seller_info',
      },
    },
  ]).exec();

localField is the field in the collection being aggregated, foreign field the field in the collection containing the looked up documents. See this link for the documentation.

Also, assert that the value of the from attribute matches the name of the target collection exactly.

CodePudding user response：

the issue was the name of the collection.

Seller.collection.collectionName holds the collection name which was sellers so plural and lowercase.

  const aggregateOrdersWithSellerInfo = await Order.aggregate([
    {
      $lookup: {
        from: 'sellers',
        localField: 'seller',
        foreignField: '_id',
        as: 'seller_info',
      },
    },
  ]).exec();