如果ObjectId为null，如何从集合中检索文档

Question

我有两个集合FormSubmissions和SubmissionLogs

FormSubmissions模式具有以下记录

{
"_id" : ObjectId("5b56bc8b9b16f0172828f951"),
"LanguageId" : ObjectId("5a6304ffc3c3f119fc0e60c9"),
"FolderName" : "89cd1929-bff9-9a6c-a60b-08bec5966d13",
"IsSync" : false,
"UpdatedBy" : 0,
"IsCompleted" : true,
"IsDeleted" : false,
"EmployeeId" : ObjectId("5ac5b80b19e1011e64766698"),
"Longitude" : null,
"Latitude" : null,
"DeviceId" : "null",
"AppInfoId" : null,
"__v" : 0
}{
"_id" : ObjectId("5b56c06a9b16f0172828f956"),
"LanguageId" : ObjectId("5a6304ffc3c3f119fc0e60c9"),
"FolderName" : "05f79cc4-ae85-7bab-4882-9dac416063f9",
"IsSync" : false,
"UpdatedBy" : 0,
"IsCompleted" : true,
"IsDeleted" : false,
"EmployeeId" : ObjectId("5b56bc429b16f0172828f94d"),
"Longitude" : null,
"Latitude" : null,
"DeviceId" : "null",
"AppInfoId" : null,
"__v" : 0
}{
"_id" : ObjectId("5b56ef414efb640d104105f9"),
"LanguageId" : ObjectId("5a6304ffc3c3f119fc0e60c9"),
"FolderName" : "d7219aff-3b8d-2931-8ca7-b790e0d1b1f4",
"IsSync" : false,
"UpdatedBy" : 0,
"IsCompleted" : true,
"IsDeleted" : false,
"EmployeeId" : ObjectId("5b56bc429b16f0172828f94d"),
"Longitude" : null,
"Latitude" : null,
"DeviceId" : "null",
"AppInfoId" : null,
"__v" : 0
}{
"_id" : ObjectId("5b56ef4d4efb640d104105fd"),
"LanguageId" : ObjectId("5a6304ffc3c3f119fc0e60c9"),
"FolderName" : "ca213664-976e-8c5d-6d3c-272931ad2dfa",
"IsSync" : false,
"UpdatedBy" : 0,
"IsCompleted" : true,
"IsDeleted" : false,
"EmployeeId" : ObjectId("5b56bc429b16f0172828f94d"),
"Longitude" : null,
"Latitude" : null,
"DeviceId" : "null",
"AppInfoId" : null,
"__v" : 0
}{
"_id" : ObjectId("5b595c2999c26d061ceff3d1"),
"LanguageId" : ObjectId("5a6304ffc3c3f119fc0e60c9"),
"FolderName" : "cde6767f-ebf2-6892-97d8-d8c9339b389d",
"IsSync" : false,
"UpdatedBy" : 0,
"IsCompleted" : true,
"IsDeleted" : false,
"EmployeeId" : null,
"Longitude" : null,
"Latitude" : null,
"DeviceId" : "null",
"AppInfoId" : null,
"__v" : 0
}

SubmissionLogs模式具有以下记录

{
"_id" : ObjectId("5b56bc8c9b16f0172828f954"),
"FormId" : ObjectId("5b56bbb19b16f0172828f948"),
"SubmissionId" : ObjectId("5b56bc8b9b16f0172828f951"),
"EmployeeId" : ObjectId("5ac5b80b19e1011e64766698"),
"UpdatedOn" : ISODate("2018-07-24T05:43:40.290Z"),
"CreatedOn" : ISODate("2018-07-24T05:43:40.290Z"),
"__v" : 0
}{
"_id" : ObjectId("5b56c06b9b16f0172828f959"),
"FormId" : ObjectId("5b56bbb19b16f0172828f948"),
"SubmissionId" : ObjectId("5b56c06a9b16f0172828f956"),
"EmployeeId" : ObjectId("5b56bc429b16f0172828f94d"),
"UpdatedOn" : ISODate("2018-07-24T06:00:11.449Z"),
"CreatedOn" : ISODate("2018-07-24T06:00:11.449Z"),
"__v" : 0
}{
"_id" : ObjectId("5b56ef414efb640d104105fc"),
"FormId" : ObjectId("5b56bbb19b16f0172828f948"),
"SubmissionId" : ObjectId("5b56ef414efb640d104105f9"),
"EmployeeId" : ObjectId("5b56bc429b16f0172828f94d"),
"UpdatedOn" : ISODate("2018-07-24T09:20:01.930Z"),
"CreatedOn" : ISODate("2018-07-24T09:20:01.930Z"),
"__v" : 0
}{
"_id" : ObjectId("5b56ef4d4efb640d10410600"),
"FormId" : ObjectId("5b56bbb19b16f0172828f948"),
"SubmissionId" : ObjectId("5b56ef4d4efb640d104105fd"),
"EmployeeId" : ObjectId("5b56bc429b16f0172828f94d"),
"UpdatedOn" : ISODate("2018-07-24T09:20:13.948Z"),
"CreatedOn" : ISODate("2018-07-24T09:20:13.948Z"),
"__v" : 0
}{
"_id" : ObjectId("5b595c2999c26d061ceff3d4"),
"FormId" : ObjectId("5b56bbb19b16f0172828f948"),
"SubmissionId" : ObjectId("5b595c2999c26d061ceff3d1"),
"EmployeeId" : null,
"UpdatedOn" : ISODate("2018-07-26T05:29:13.991Z"),
"CreatedOn" : ISODate("2018-07-26T05:29:13.991Z"),
"__v" : 0
}

我只想要我正在传递EmployeeId的SubmissionLogs(也在数组null中(

为此，我要这么做，

db.FormSubmissions.aggregate(
[{
$match : {
$and : [{
FormId : ObjectId("5b56bbb19b16f0172828f948")
}, {
IsDeleted : false
}
]
}
}, {
$lookup : {
from : "SubmissionLogs",
localField : "_id",
foreignField : "SubmissionId",
as : "SubmissionLogs"
}
}, {
$addFields : {
SubmissionLog : {
$filter : {
input : "$SubmissionLogs",
as : "submissionLogs_field",
cond : {
$setIsSubset : [
["$$submissionLogs_field.EmployeeId"],
["5b56bc429b16f0172828f94d", null]
]
}
}
}
}
}, {
$project : {
SubmissionLog : "$SubmissionLog"
}
}
]).pretty()

我只想要SubmissionLogs中有["5b56bc429b16f0172828f94d", null]EmployeeId的记录。如果Employe_Id为null，我也想要那些记录。

Answer 1

在中运行良好$group$push$

db.FormSubmissions.aggregate([
{
$match : { IsDeleted : false}
}, 
{
$lookup : {
from : "SubmissionLogs",
localField : "_id",
foreignField : "SubmissionId",
as : "SubmissionLogs"
}
}, 
{
"$unwind" : "$SubmissionLogs"
},
{
"$match" : {"SubmissionLogs.EmployeeId" : {"$in" : [ObjectId("5b56bc429b16f0172828f94d"), null]}}
},
{"$group" : {"_id" : "$_id",SubmissionLog : {"$push" : "$SubmissionLogs"}}}
])