database - Mongo - add field if object in array of sub docs has value

问题描述

Details

I develop survey application with express and struggle with some getting of data.

The case:

you can get all surveys by "GET /surveys". And every survey doc has to contains hasVoted:mongoose.Bool and optionsVote:mongoose.Map if the user has voted for the survey. (SurveySchema is bellow)
you can vote for survey by "POST /surveys/vote"
you can see the results of any survey only if you vote for it

 new Schema({
    question: {
        type: mongoose.Schema.Types.String,
        required: true,
    },
    options: {
        type: [{
            type: mongoose.Schema.Types.String,
            required: true,
        }]
    },
    optionsVote: {
        type: mongoose.Schema.Types.Map,
        of: mongoose.Schema.Types.Number,
    },
    votesCount: {
        type: mongoose.Schema.Types.Number,
    },
    votes: {
        type: [{
            user: {
                type: mongoose.Schema.Types.ObjectId,
                ref: 'User',
            },
            option: mongoose.Schema.Types.Number,
        }]
    },
})

Target:

So the target of the question is how to add fields hasVoted and optionsVote if there is "Vote" sub document in votes array where user===req.user.id ?

I believe you got the idea so if you have an idea how to change the schema to achieve the desired result I'm open!

Example:

Data:

[{
 id:"surveyId1
 question:"Question",
 options:["op1","op2"],
 votes:[{user:"userId1", option:0}]
 votesCount:1,
 optionsVote:{"0":1,"1":0}
},{
 id:"surveyId2
 question:"Question",
 options:["op1","op2"],
 votes:[{user:"userId2", option:0}]
 votesCount:1,
 optionsVote:{"0":1,"1":0}
}]

Route handler:

Where req.user.id='userId1' and then make the desired query.

The result

[{ // Voted for this survey
 id:"surveyId1
 question:"Question",
 options:["op1","op2"],
 votes:[{user:"userId1", option:0}]
 votesCount:1,
 optionsVote:{"0":1,"1":0},
 hasVoted:true,
},{ // No voted for this survey
 id:"surveyId2
 question:"Question",
 options:["op1","op2"],
 votesCount:1,
}]

标签： databasemongodbmongoosemongodb-queryaggregation-framework

解决方案

在 MongoDB 中，您可以按如下方式搜索子文档

//Mongodb query to search for survey filled by a user
db.survey.find({ 'votes.user': myUserId })

因此，当您只能在用户投票的地方获得结果时，您真的需要hasVoted字段吗？

要拥有optionsVote字段，首先我更喜欢optionsVoteas的架构{option: "a", count:1}。您可以选择以下任何一种方法。

A.optionsVote在更新时通过增加投票选项的计数来管理更新字段POST /survey/vote。

B. 另一种方法是optionsVote根据时的votes条目来计算GET /survey。您可以通过聚合来做到这一点

//Mongodb query to get optionsVote:{option: "a", count:1} from votes: { user:"x", option:"a"}
db.survey.aggregate([
    { $unwind: "$votes" }, 
    { $group: {
        "_id": { "id": "_id", "option": "$votes.option" }, 
       optionCount: { $sum: 1 }
       }
    },
    {
      $group: { "_id": "$_id.id" },
      optionsVote: { $push : { option: "$_id.option", count: "$optionCount" } },
      votes: { $push : '$votes'}
    }
])
//WARNING: I haven't tested this query, this is just to show the approach -> group based on votes.option and count all votes for that option for each document and then create optionsVote field by pushing all option with their count using $push into the field `optionsVote`

我推荐方法 A，因为我认为 POST 操作会比 GET 操作少得多。也更容易实现。话虽如此，将查询放在 B 方便将有助于您进行完整性检查。

database - Mongo - add field if object in array of sub docs has value

问题描述

解决方案

推荐阅读