java - 如何获取具有匹配条件的 mongo 结果以及嵌入式文档数组中特定 id 的验证

Question

我想根据特定条件显示所有匹配的记录，同时我需要检查每个记录的嵌套数组中是否存在某个用户以及注册用户的数量(嵌套数组计数)。

我的样本集合示例:

"_id" : ObjectId("583fb6c9e17e8c1bf80da30e"),
"name" : "Arijt Singh Live",
"category" : {
    "_id" : ObjectId("583dace6e17e4db43e29dd44"),
    "name" : "Music"
},
"schedule" : {
    "date" : ISODate("2016-12-16T03:30:00.000Z"),
    "start_time" : ISODate("2016-12-16T03:30:00.000Z"),
    "end_time" : ISODate("2016-12-16T10:30:00.000Z")
},
"celebrity" : {
    "_id" : ObjectId("583dac6ee17e4db43e29dd3e"),
    "name" : "Chiranjeevi",
    "profilePic" : "http://sample.com/"
},
"online_moderator" : {
    "_id" : ObjectId("583dac9ce17e4db43e29dd40"),
    "name" : "Suma",
    "profilePic" : "http://sample.com/",
    "id" : ObjectId("583dac9ce17e4db43e29dd40")
},
"offline_moderator" : {
    "_id" : ObjectId("583daebae17e4db43e29dd48"),
    "name" : "PriyaRam",
    "roomUrl" : "http://sample.com/link",
    "roomName" : "PriyaRam_1480437433704",
    "roomKey" : "uJcJqV5D8j",
    "roomId" : "146547",
    "profilePic" : "http://sample.com/",
    "id" : ObjectId("583daebae17e4db43e29dd48")
},
"region" : {
    "code" : "IN",
    "name" : "India"
},
"status" : "active",
"event_state" : "live",
"registered_users" : [ 
    {
        "user_id" : ObjectId("583dac6ee17e4db43e29dd3e"),
        "user_name" : "Chiranjeevi",
        "profile_pic" : "http://sample.com/",
        "registered_date" : ISODate("2016-12-01T05:36:00.000Z"),
        "is_celebrity" : "true",
        "event_default_user" : "true",
        "user_call_status" : "joined",
        "audio_status" : "false",
        "video_status" : "false"
    }, 
    {
        "user_id" : ObjectId("583dac9ce17e4db43e29dd40"),
        "user_name" : "Suma",
        "profile_pic" : "http://sample.com/",
        "registered_date" : ISODate("2016-12-01T05:36:00.000Z"),
        "is_celebrity" : "false",
        "event_default_user" : "true",
        "user_call_status" : "registered",
        "audio_status" : "false",
        "video_status" : "false"
    }, 
    {
        "user_id" : ObjectId("583daebae17e4db43e29dd48"),
        "user_name" : "PriyaRam",
        "profile_pic" : "http://sample.com/",
        "registered_date" : ISODate("2016-12-01T05:36:00.000Z"),
        "is_celebrity" : "false",
        "event_default_user" : "true",
        "user_call_status" : "registered",
        "audio_status" : "false",
        "video_status" : "false"
    }, 
    {
        "user_id" : ObjectId("583e9719e17e8c1bf80da2fe"),
        "user_name" : "UserSurya",
        "profile_pic" : "siri",
        "registered_date" : ISODate("2016-11-30T12:32:00.000Z"),
        "is_celebrity" : "false",
        "event_default_user" : "false",
        "user_call_status" : "joined",
        "audio_status" : "false",
        "video_status" : "false",
        "participantId" : ""
    },  
    {
        "user_id" : ObjectId("584a47abe17e6e3ee8d7c645"),
        "user_name" : "Given",
        "profile_pic" : "asset.JPG",
        "registered_date" : ISODate("2016-12-09T06:07:00.000Z"),
        "is_celebrity" : "false",
        "event_default_user" : "false",
        "user_call_status" : "registered",
        "audio_status" : "false",
        "video_status" : "false"
    }, 
    {
        "user_id" : ObjectId("584e6253e17ed10f0a8cba1d"),
        "user_name" : "Manoj",
        "profile_pic" : "(null)",
        "registered_date" : ISODate("2016-12-12T08:40:00.000Z"),
        "is_celebrity" : "false",
        "event_default_user" : "false",
        "user_call_status" : "registered",
        "audio_status" : "false",
        "video_status" : "false"
    }
],
"recorder_id" : "269894",
"webcast_url" : "http://sample.com/link"

我编写了以下查询:

db.events.aggregate([
    { $match: { $and: [ 
        { "event_state" : {$in : ["live","scheduled"]} }, 
        { "schedule.end_time" : {$gt : ISODate("2016-12-12T06:30:00.000Z")} }
        ] 
      } 
    },
    {$project: 
        { 
            "name" :1, 
            "category" :1,
            "schedule":1, 
            "celebrity" :1,
            "online_moderator" :1,
            "offline_moderator" :1,
            "region" :1,
            "status" :1,
            "event_state" :1,
            "recorder_id" :1,
            "webcast_url":1,
            "replay_url":1,
            "registered_users":1,
            registeredUsers: { $size:"$registered_users" },
            is_registered:
               {
                 $cond: { if: { $eq: [ "registered_users.user_id", ObjectId("584e6253e17ed10f0a8cba1d") ] }, then: 1, else: 0 }
               }
        }
    }
]);

它给了我真正想要的部分结果。

在上面的示例中，如果我需要检查是否有 ID 注册此事件的用户，那么我的查询应返回结果集以及注册状态，如下所示。

"_id" : ObjectId("583fb6c9e17e8c1bf80da30e"),
    "name" : "Arijt Singh Live",
    "category" : {
        "_id" : ObjectId("583dace6e17e4db43e29dd44"),
        "name" : "Music"
    },
    "schedule" : {
        "date" : ISODate("2016-12-16T03:30:00.000Z"),
        "start_time" : ISODate("2016-12-16T03:30:00.000Z"),
        "end_time" : ISODate("2016-12-16T10:30:00.000Z")
    },
    "celebrity" : {
        "_id" : ObjectId("583dac6ee17e4db43e29dd3e"),
        "name" : "Chiranjeevi",
        "profilePic" : "http://sample.com/app/uploads/profilepics/"
    },
    "online_moderator" : {
        "_id" : ObjectId("583dac9ce17e4db43e29dd40"),
        "name" : "Suma",
        "profilePic" : "http://sample.com/app/uploads/profilepics/",
        "id" : ObjectId("583dac9ce17e4db43e29dd40")
    },
    "offline_moderator" : {
        "_id" : ObjectId("583daebae17e4db43e29dd48"),
        "name" : "PriyaRam",
        "roomUrl" : "http://sample.com/link",
        "roomName" : "PriyaRam_1480437433704",
        "roomKey" : "uJcJqV5D8j",
        "roomId" : "146547",
        "profilePic" : "http://sample.com/app/uploads/profilepics/",
        "id" : ObjectId("583daebae17e4db43e29dd48")
    },
    "region" : {
        "code" : "IN",
        "name" : "India"
    },
    "status" : "active",
    "event_state" : "live",    
    "recorder_id" : "269894",
    "webcast_url" : "http://sample.com/link",
    "registeredUsers" : 9,
    "is_registered": 1

在上面的示例中，如果您看到“registeredUsers”和“is_registered”不是原始文档的一部分。但我正试图把它找出来。

Answer 1

is_registered 字段可以通过在 $project 阶段添加以下表达式来计算。使用 $setIsSubset 通过比较两个数组，当第一个数组是第二个数组的子集时返回 true。

is_registered:
           { $setIsSubset: [[ObjectId("584e6253e17ed10f0a8cba1d")], "$registered_users.user_id"]}