MongoDB Lookup with Count

Question

对于我们的非营利组织，我们在夏季招聘工人，并在Mongo数据库中跟踪应用程序信息。我试图将查询结果限制为仅针对当前夏季的申请，但要确定申请人完成了多少个夏季。对于查询，我有两个集合:申请人和应用程序。

这是应用程序集合的样子:

{
"_id": {
"$oid": "6324c900ba6fad569a175778"
},
"Applicant": {
"ID": {
"$oid": "6324c900ba6fad569a175777"
}
},
"Summer": {
"ID": {
"$oid": "932567532532d278b614742a"
}
},
"Status": "Hired",
...
},
{
"_id": {
"$oid": "6424c900ba6fad569a175765"
},
"Applicant": {
"ID": {
"$oid": "6324c900ba6fad5d9a175777"
}
},
"Summer": {
"ID": {
"$oid": "5624c900ba6fadd59a17577e"
}
},
"Status": "Complete",
...
}

这是申请人集合中的一个示例:

{
"_id": {
"$oid": "6324c900ba6fad5d9a175777"
},
"FirstName": "Ted",  
"LastName": "Doe",
"CellPhoneNumber": "1234567890",
"HomePhoneNumber": null,
"EmailAddress": "[e-mail]",  
"MailingAddress": {
"Address1": "123 Some Rd",
"Address2": "",
"City": "Somewhere",
"State": "TX",
"PostalCode": "23334",
"Country": "United States"
},
...
}

Application集合包括_id, Summer。ID,申请人。ID，状态等，而申请人收集包括_id, FirstName, LastName, EmailAddress等。我想要得到的是一个筛选过的应用程序列表，每个当前申请人有一行，显示姓名，LastName，当前状态，电子邮件地址等，以及这个人所有具有"完成"的应用程序的计数。的地位。输出的示例如下:

First Last  Status    EmailAddress         Completed Summers
John  Doe   Pending   [e-mail]   3
Jane  Smith Hired     [e-mail]  2
Fred  Smith Withdrawn [e-mail]   0
Ted   Doe   Hired     [e-mail]    2
Sally So    Completed [e-mail]  3

使用下面的查询，我可以得到除了完成的夏季的计数以外的所有内容。

[
{
$match: {
Status: "Hired",
"Summer.ID": ObjectId(
"932567532532d278b614742a"
),
},
},
{
$lookup: {
from: "Applicants",
localField: "Applicant.ID",
foreignField: "_id",
as: "Applicant",
},
},
{
$unwind: "$Applicant",
},
{
$project: {
_id: 1,
FirstName: 1,
LastName: 1,
Status: 1,
Phone: "$Applicant.CellPhoneNumber",
Email: "$Applicant.EmailAddress",
},
},
]

从这里开始，我不知道如何进行。

我尝试了一种不同的方法来帮助我获得计数，但是当我试图从申请人集合中获取查找信息时，我失败了。

[
{
$match: {
$or: [
{
Status: "Hired",
},
{
Status: "Summer Complete",
},
],
},
},
{
$group: {
_id: "$Applicant.ID",
summers: {
$sum: 1,
},
},
},
{
$lookup: {
from: "Applicants",
localField: "Applicant.ID",
foreignField: "_id",
as: "Applicants",
},
},
{ $unwind: "$Applicants" },
{
$replaceRoot: {
newRoot: {
$mergeObjects: [
{ summers: "$summers" },
"$Applicants",
],
},
},
},
]

解决这个查询的最佳方法是什么?

Answer 1

您需要的是另一个查找阶段，以从与申请人ID匹配的应用程序集合中检索夏季的计数，这些夏季不是当前的夏季，并且状态为"完成"。

{
$lookup: {
from: "Applications",
localField: "Applicant._id",
foreignField: "Applicant.ID",
as: "summers",
pipeline: [
{
$match: {
"Summer.ID": {
$ne: ObjectId("932567532532d278b614742a")
},
Status: "Complete"
}
},
{
$count: "count"
}
]
}
},
{
$unwind: "$summers"
},
{
$project: {
_id: 1,
FirstName: 1,
LastName: 1,
Status: 1,
Phone: "$Applicant.CellPhoneNumber",
Email: "$Applicant.EmailAddress",
PreviousSummers: "$summers.count"
},

}

游乐场