mongodb - 聚合后 Mongoose 填充

Question

在运行聚合管道然后填充后，我试图获得特定的数据模型，但我只是达不到它。

最终想要的结果如下:

[
  {
    _accountId: "5beee0966d17bc42501f1234",
    name: "Company Name 1",
    contactEmail: "email1@email.com",
    contactName: "contact Name 1"
    reason: "Warranties",
    total: 1152,
    lineItems: [
      {
        _id: "5beee0966d17bc42501f5086",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      },
      {
        _id: "5bf43929e7179a56e21382bc",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      },
      {
        _id: "5bf4392fe7179a56e21382bd",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      }
    ]
  },
  {
    _accountId: "5beee0966d17bc42501f1235",
    name: "Company Name 2",
    contactEmail: "email2@email.com",
    contactName: "contact Name 2"
    reason: "Warranties",
    total: 1152,
    lineItems: [
      {
        _id: "5beee0966d17bc42501f5086",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      },
      {
        _id: "5bf43929e7179a56e21382bc",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      },
      {
        _id: "5bf4392fe7179a56e21382bd",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      }
    ]
  }
]

我正在从以下两个模型中收集这些数据:

保修

{
  _id: "5beee0966d17bc42501f5086",
  jobsiteAddressStreet: String,
  jobsiteAddressCity: String,
  jobsiteAddressState" String,
  jobsiteAddressZip: Number,
  warrantyFee: Number,
  _accountId: {
    type: Schema.Types.ObjectId,
    ref: "accounts"
  },
  payStatus: String
}

帐号

{
  _id: "5beee0966d17bc42501f1235",
  name: String,
  contactName: String,
  contactEmail: String
}

我目前的查询如下:

Warranty.aggregate([
    {
      $match: {
        payStatus: "Invoiced Next Billing Cycle"
      }
    },
    {
      $group: {
        _id: "$_accountId",
        total: {
          $sum: "$warrantyFee"
        },
        lineItems: {
          $push: {
            _id: "$_id",
            jobsiteAddress: {
              $concat: [
                "$jobsiteAddressStreet",
                " ",
                "$jobsiteAddressCity",
                ", ",
                "$jobsiteAddressState",
                " ",
                "$jobsiteAddressZip"
              ]
            },
            warrantyFee: "$warrantyFee"
          }
        }
      }
    },
    {
      $project: {
        reason: "Warranties",
        total: "$total",
        lineItems: "$lineItems"
      }
    }
  ])
    .then(warranties => {
      console.log(warranties);
      Account.populate(warranties, {
        path: "_id",
        select: "contactName contactEmail name"
      })
        .then(warranties => {
          res.send(warranties);
        })
        .catch(err => {
          res.status(422).send(err);
          throw err;
        });
    })
    .catch(err => {
      res.status(422).send(err);
      throw err;
    });

结果如下:

[
  {
    _id: {
      _id: "5bc39dfa331c0e2cb897b61e",
      name: "Company Name 1",
      contactEmail: "email1@email.com",
      contactName: "Contact Name 1"
    },
    reason: "Warranties",
    total: 1152,
    lineItems: [
      {
        _id: "5beee0966d17bc42501f5086",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      },
      {
        _id: "5bf43929e7179a56e21382bc",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      },
      {
        _id: "5bf4392fe7179a56e21382bd",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      }
    ]
  },
  {
    _id: {
      _id: "5bc39dfa331c0e2cb897b61e",
      name: "Company Name 2",
      contactEmail: "email2@email.com",
      contactName: "Contact Name 2"
    },
    reason: "Warranties",
    total: 1152,
    lineItems: [
      {
        _id: "5beee0966d17bc42501f5086",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      },
      {
        _id: "5bf43929e7179a56e21382bc",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      },
      {
        _id: "5bf4392fe7179a56e21382bd",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      }
    ]
  }
]

正如您所看到的，这与一些小问题非常接近。

它显示 _id 而不是 _accountId。我默认这样做是因为每当我尝试在 $group 中返回 _accountId 时，它都会将其标记为非累加器字段，而当我在 $project 中执行此操作时，它只是不显示。数据集必须按保修模型中的 _accountId 分组。

如果可能，我更愿意将额外的(contactName、contactEmail、name)字段添加到顶级对象，而不是创建子文档。这可能很简单或不可能，因为我对 populate 不是很熟悉，但找不到任何可以直接回答我的问题的内容。

最后的目标是获取返回的对象并使用对象数组将文档批量创建到另一个集合。

-- 回答我的具体用例 --

Warranty.aggregate([
    {
      $match: {
        payStatus: "Invoiced Next Billing Cycle"
      }
    },
    {
      $group: {
        _id: "$_accountId",
        total: {
          $sum: "$warrantyFee"
        },
        lineItems: {
          $push: {
            _id: "$_id",
            jobsiteAddress: {
              $concat: [
                "$jobsiteAddressStreet",
                " ",
                "$jobsiteAddressCity",
                ", ",
                "$jobsiteAddressState",
                " ",
                "$jobsiteAddressZip"
              ]
            },
            warrantyFee: "$warrantyFee"
          }
        }
      }
    },
    {
      $lookup: {
        from: Account.collection.name,
        localField: "_id",
        foreignField: "_id",
        as: "accounts"
      }
    },
    {
      $unwind: "$accounts"
    },
    {
      $project: {
        lineItems: "$lineItems",
        reason: "Warranties",
        total: "$total",
        type: "Invoice",
        date: new Date(),
        company: "$accounts.name",
        contactName: "$accounts.contactName",
        contactEmail: "$accounts.contactEmail"
      }
    },
    {
      $addFields: {
        _accountId: "$_id"
      }
    },
    {
      $project: {
        _id: 0
      }
    }
  ])

这给了我结果:

[
  {
    lineItems: [
      {
        _id: "5be203eb3afd8098d4988152",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      }
    ],
    reason: "Warranties",
    total: 384,
    type: "Invoice",
    date: "2018-11-21T14:08:15.052Z",
    company: "Company Name 1",
    contactName: "Contact Name 1",
    contactEmail: "email1@email.com",
    _accountId: "5be203eb3afd8098d4988152",
    referenceNumber: 1542809296615
  },
  {
    lineItems: [
      {
        _id: "5beee0966d17bc42501f5086",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      },
      {
        _id: "5bf43929e7179a56e21382bc",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      },
      {
        _id: "5bf4392fe7179a56e21382bd",
        jobsiteAddress: "1234 Street Southwest Sunnyville, Wyoming 12345",
        warrantyFee: 384
      }
    ],
    reason: "Warranties",
    total: 1152,
    type: "Invoice",
    date: "2018-11-21T14:08:15.052Z",
    company: "Company Name 2",
    contactName: "Contact Name 2",
    contactEmail: "email2@email.com",
    _accountId: "5bc39dfa331c0e2cb897b61e",
    referenceNumber: 1542809295680
  }
]

Answer 1

因此，当您要求“填充”聚合结果时，您实际上在这里遗漏了一些概念。通常这不是您实际执行的操作，而是解释要点:

aggregate()的输出不像 Model.find()或类似的行动，因为这里的目的是“重塑结果”。这基本上意味着您用作聚合源的模型不再被视为输出模型。如果您在输出时仍然保持完全相同的文档结构，这甚至是正确的，但在您的情况下，输出显然与源文档不同。

无论如何它不再是 Warranty 的实例您正在采购的模型，但只是一个普通对象。我们可以在稍后讨论时解决这个问题。

这里的重点可能是populate()反正有点“老帽子”。这实际上只是在实现初期添加到 Mongoose 的便利功能。它真正做的只是对单独集合中的相关数据执行“另一个查询”，然后将内存中的结果合并到原始集合输出中。

出于很多原因，在大多数情况下，这并不是真正有效的，甚至是不可取的。与流行的误解相反，这是不是 实际上是“加入”。

对于真正的“加入”，您实际上使用 $lookup 聚合管道阶段，MongoDB 使用该阶段从另一个集合返回匹配项。不像 populate()这实际上是在对服务器的单个请求中通过单个响应完成的。这避免了网络开销，通常更快，并且作为“真正的加入”允许您做populate() 的事情。无法做到。

改用 $lookup

这里缺少的非常快速的版本是，而不是尝试 populate()在 .then()返回结果后，您要做的是添加 $lookup 到管道:

  { "$lookup": {
    "from": Account.collection.name,
    "localField": "_id",
    "foreignField": "_id",
    "as": "accounts"
  }},
  { "$unwind": "$accounts" },
  { "$project": {
    "_id": "$accounts",
    "total": 1,
    "lineItems": 1
  }}

请注意，这里有一个约束，即 $lookup 的输出是 总是 数组。是否只有一项相关项或许多项要作为输出获取并不重要。管道阶段将查找 "localField" 的值来自当前文档并使用它来匹配 "foreignField" 中的值指定的。在这种情况下，它是 _id来自聚合 $group 目标为 _id国外收藏。

由于输出始终是前面提到的数组，因此对于此实例使用它的最有效方法是简单地添加 $unwind 紧接在 $lookup 之后的舞台.所有这些都会为目标数组中返回的每个项目返回一个新文档，在这种情况下，您希望它是一个。在 _id的情况下在外部集合中不匹配，没有匹配的结果将被删除。

需要注意的是，这实际上是一种优化模式，如 $lookup + $unwind Coalescence 中所述。在核心文档中。这里发生了一件特别的事情，其中​​ $unwind 指令实际上合并到 $lookup 以高效的方式运作。你可以在那里阅读更多相关信息。

使用填充

从上面的内容你应该可以基本明白为什么 populate()这是错误的做法。除了输出不再由 Warranty 组成的基本事实之外模型对象，该模型实际上只知道 _accountId 中描述的外部项目无论如何都不存在于输出中的属性。

现在您实际上可以定义一个模型，该模型可用于将输出对象显式转换为定义的输出类型。一个简短的演示将涉及向您的应用程序添加代码，例如:

// Special models

const outputSchema = new Schema({
  _id: { type: Schema.Types.ObjectId, ref: "Account" },
  total: Number,
  lineItems: [{ address: String }]
});

const Output = mongoose.model('Output', outputSchema, 'dontuseme');

这个新 Output然后可以使用模型将生成的纯 JavaScript 对象“转换”到 Mongoose 文档中，以便像 Model.populate() 这样的方法。实际上可以称为:

// excerpt
result2 = result2.map(r => new Output(r));   // Cast to Output Mongoose Documents

// Call populate on the list of documents
result2 = await Output.populate(result2, { path: '_id' })
log(result2);

自 Output定义了一个架构，它知道 _id 上的“引用”它的文档领域 Model.populate()知道它需要做什么并返回项目。

不过要小心，因为这实际上会生成另一个查询。 IE:

Mongoose: warranties.aggregate([ { '$match': { payStatus: 'Invoiced Next Billing Cycle' } }, { '$group': { _id: '$_accountId', total: { '$sum': '$warrantyFee' }, lineItems: { '$push': { _id: '$_id', address: { '$trim': { input: { '$reduce': { input: { '$objectToArray': '$address' }, initialValue: '', in: { '$concat': [ '$$value', ' ', [Object] ] } } }, chars: ' ' } } } } } } ], {})
Mongoose: accounts.find({ _id: { '$in': [ ObjectId("5bf4b591a06509544b8cf75c"), ObjectId("5bf4b591a06509544b8cf75b") ] } }, { projection: {} })

其中第一行是聚合输出，然后您再次联系服务器以返回相关 Account模型条目。

概括

所以这些是你的选择，但很明显，现代方法是使用 $lookup 并获得真正的“加入”，这不是 populate()实际上是在做。

包括一个 list ，作为对这些方法中的每一种在实践中如何实际工作的完整演示。这里采用了一些艺术许可，因此所展示的模型可能与您拥有的模型不完全相同，但是那里足以以可复制的方式展示基本概念:

const { Schema } = mongoose = require('mongoose');

const uri = 'mongodb://localhost:27017/joindemo';
const opts = { useNewUrlParser: true };

// Sensible defaults
mongoose.Promise = global.Promise;
mongoose.set('debug', true);
mongoose.set('useFindAndModify', false);
mongoose.set('useCreateIndex', true);

// Schema defs

const warrantySchema = new Schema({
  address: {
    street: String,
    city: String,
    state: String,
    zip: Number
  },
  warrantyFee: Number,
  _accountId: { type: Schema.Types.ObjectId, ref: "Account" },
  payStatus: String
});

const accountSchema = new Schema({
  name: String,
  contactName: String,
  contactEmail: String
});

// Special models


const outputSchema = new Schema({
  _id: { type: Schema.Types.ObjectId, ref: "Account" },
  total: Number,
  lineItems: [{ address: String }]
});

const Output = mongoose.model('Output', outputSchema, 'dontuseme');

const Warranty = mongoose.model('Warranty', warrantySchema);
const Account = mongoose.model('Account', accountSchema);


// log helper
const log = data => console.log(JSON.stringify(data, undefined, 2));

// main
(async function() {

  try {

    const conn = await mongoose.connect(uri, opts);

    // clean models
    await Promise.all(
      Object.entries(conn.models).map(([k,m]) => m.deleteMany())
    )

    // set up data
    let [first, second, third] = await Account.insertMany(
      [
        ['First Account', 'First Person', 'first@example.com'],
        ['Second Account', 'Second Person', 'second@example.com'],
        ['Third Account', 'Third Person', 'third@example.com']
      ].map(([name, contactName, contactEmail]) =>
        ({ name, contactName, contactEmail })
      )
    );

    await Warranty.insertMany(
      [
        {
          address: {
            street: '1 Some street',
            city: 'Somewhere',
            state: 'TX',
            zip: 1234
          },
          warrantyFee: 100,
          _accountId: first,
          payStatus: 'Invoiced Next Billing Cycle'
        },
        {
          address: {
            street: '2 Other street',
            city: 'Elsewhere',
            state: 'CA',
            zip: 5678
          },
          warrantyFee: 100,
          _accountId: first,
          payStatus: 'Invoiced Next Billing Cycle'
        },
        {
          address: {
            street: '3 Other street',
            city: 'Elsewhere',
            state: 'NY',
            zip: 1928
          },
          warrantyFee: 100,
          _accountId: first,
          payStatus: 'Invoiced Already'
        },
        {
          address: {
            street: '21 Jump street',
            city: 'Anywhere',
            state: 'NY',
            zip: 5432
          },
          warrantyFee: 100,
          _accountId: second,
          payStatus: 'Invoiced Next Billing Cycle'
        }
      ]
    );

    // Aggregate $lookup
    let result1 = await Warranty.aggregate([
      { "$match": {
        "payStatus": "Invoiced Next Billing Cycle"
      }},
      { "$group": {
        "_id": "$_accountId",
        "total": { "$sum": "$warrantyFee" },
        "lineItems": {
          "$push": {
            "_id": "$_id",
            "address": {
              "$trim": {
                "input": {
                  "$reduce": {
                    "input": { "$objectToArray": "$address" },
                    "initialValue": "",
                    "in": {
                      "$concat": [ "$$value", " ", { "$toString": "$$this.v" } ] }
                  }
                },
                "chars": " "
              }
            }
          }
        }
      }},
      { "$lookup": {
        "from": Account.collection.name,
        "localField": "_id",
        "foreignField": "_id",
        "as": "accounts"
      }},
      { "$unwind": "$accounts" },
      { "$project": {
        "_id": "$accounts",
        "total": 1,
        "lineItems": 1
      }}
    ])

    log(result1);

    // Convert and populate
    let result2 = await Warranty.aggregate([
      { "$match": {
        "payStatus": "Invoiced Next Billing Cycle"
      }},
      { "$group": {
        "_id": "$_accountId",
        "total": { "$sum": "$warrantyFee" },
        "lineItems": {
          "$push": {
            "_id": "$_id",
            "address": {
              "$trim": {
                "input": {
                  "$reduce": {
                    "input": { "$objectToArray": "$address" },
                    "initialValue": "",
                    "in": {
                      "$concat": [ "$$value", " ", { "$toString": "$$this.v" } ] }
                  }
                },
                "chars": " "
              }
            }
          }
        }
      }}
    ]);

    result2 = result2.map(r => new Output(r));

    result2 = await Output.populate(result2, { path: '_id' })
    log(result2);

  } catch(e) {
    console.error(e)
  } finally {
    process.exit()
  }

})()

以及完整的输出:

Mongoose: dontuseme.deleteMany({}, {})
Mongoose: warranties.deleteMany({}, {})
Mongoose: accounts.deleteMany({}, {})
Mongoose: accounts.insertMany([ { _id: 5bf4b591a06509544b8cf75b, name: 'First Account', contactName: 'First Person', contactEmail: 'first@example.com', __v: 0 }, { _id: 5bf4b591a06509544b8cf75c, name: 'Second Account', contactName: 'Second Person', contactEmail: 'second@example.com', __v: 0 }, { _id: 5bf4b591a06509544b8cf75d, name: 'Third Account', contactName: 'Third Person', contactEmail: 'third@example.com', __v: 0 } ], {})
Mongoose: warranties.insertMany([ { _id: 5bf4b591a06509544b8cf75e, address: { street: '1 Some street', city: 'Somewhere', state: 'TX', zip: 1234 }, warrantyFee: 100, _accountId: 5bf4b591a06509544b8cf75b, payStatus: 'Invoiced Next Billing Cycle', __v: 0 }, { _id: 5bf4b591a06509544b8cf75f, address: { street: '2 Other street', city: 'Elsewhere', state: 'CA', zip: 5678 }, warrantyFee: 100, _accountId: 5bf4b591a06509544b8cf75b, payStatus: 'Invoiced Next Billing Cycle', __v: 0 }, { _id: 5bf4b591a06509544b8cf760, address: { street: '3 Other street', city: 'Elsewhere', state: 'NY', zip: 1928 }, warrantyFee: 100, _accountId: 5bf4b591a06509544b8cf75b, payStatus: 'Invoiced Already', __v: 0 }, { _id: 5bf4b591a06509544b8cf761, address: { street: '21 Jump street', city: 'Anywhere', state: 'NY', zip: 5432 }, warrantyFee: 100, _accountId: 5bf4b591a06509544b8cf75c, payStatus: 'Invoiced Next Billing Cycle', __v: 0 } ], {})
Mongoose: warranties.aggregate([ { '$match': { payStatus: 'Invoiced Next Billing Cycle' } }, { '$group': { _id: '$_accountId', total: { '$sum': '$warrantyFee' }, lineItems: { '$push': { _id: '$_id', address: { '$trim': { input: { '$reduce': { input: { '$objectToArray': '$address' }, initialValue: '', in: { '$concat': [ '$$value', ' ', [Object] ] } } }, chars: ' ' } } } } } }, { '$lookup': { from: 'accounts', localField: '_id', foreignField: '_id', as: 'accounts' } }, { '$unwind': '$accounts' }, { '$project': { _id: '$accounts', total: 1, lineItems: 1 } } ], {})
[
  {
    "total": 100,
    "lineItems": [
      {
        "_id": "5bf4b591a06509544b8cf761",
        "address": "21 Jump street Anywhere NY 5432"
      }
    ],
    "_id": {
      "_id": "5bf4b591a06509544b8cf75c",
      "name": "Second Account",
      "contactName": "Second Person",
      "contactEmail": "second@example.com",
      "__v": 0
    }
  },
  {
    "total": 200,
    "lineItems": [
      {
        "_id": "5bf4b591a06509544b8cf75e",
        "address": "1 Some street Somewhere TX 1234"
      },
      {
        "_id": "5bf4b591a06509544b8cf75f",
        "address": "2 Other street Elsewhere CA 5678"
      }
    ],
    "_id": {
      "_id": "5bf4b591a06509544b8cf75b",
      "name": "First Account",
      "contactName": "First Person",
      "contactEmail": "first@example.com",
      "__v": 0
    }
  }
]
Mongoose: warranties.aggregate([ { '$match': { payStatus: 'Invoiced Next Billing Cycle' } }, { '$group': { _id: '$_accountId', total: { '$sum': '$warrantyFee' }, lineItems: { '$push': { _id: '$_id', address: { '$trim': { input: { '$reduce': { input: { '$objectToArray': '$address' }, initialValue: '', in: { '$concat': [ '$$value', ' ', [Object] ] } } }, chars: ' ' } } } } } } ], {})
Mongoose: accounts.find({ _id: { '$in': [ ObjectId("5bf4b591a06509544b8cf75c"), ObjectId("5bf4b591a06509544b8cf75b") ] } }, { projection: {} })
[
  {
    "_id": {
      "_id": "5bf4b591a06509544b8cf75c",
      "name": "Second Account",
      "contactName": "Second Person",
      "contactEmail": "second@example.com",
      "__v": 0
    },
    "total": 100,
    "lineItems": [
      {
        "_id": "5bf4b591a06509544b8cf761",
        "address": "21 Jump street Anywhere NY 5432"
      }
    ]
  },
  {
    "_id": {
      "_id": "5bf4b591a06509544b8cf75b",
      "name": "First Account",
      "contactName": "First Person",
      "contactEmail": "first@example.com",
      "__v": 0
    },
    "total": 200,
    "lineItems": [
      {
        "_id": "5bf4b591a06509544b8cf75e",
        "address": "1 Some street Somewhere TX 1234"
      },
      {
        "_id": "5bf4b591a06509544b8cf75f",
        "address": "2 Other street Elsewhere CA 5678"
      }
    ]
  }
]