How to conditionally set a new field on all sub-documents in MongoDB collection

user_7489488 0
Viewed 104

I'm using MongoDB version 3.4.9. I have the following MongoDB schema:

const schema = new mongoose.Schema({
    reputationHistory: [ reputationHistorySchema ],
});

const reputationHistorySchema = new mongoose.Schema({
    isBid: { type: Boolean, default: false, required: true },
    isResult: { type: Boolean, default: false, required: true },
});

This is an example of what it looks like:

[{
    reputationHistory: [{
        isBid: true,
        isResult: false,
    }, {
        isBid: false,
        isResult: true,
    }]
}, {
    reputationHistory: [{
        isBid: true,
        isResult: false,
    }, {
        isBid: false,
        isResult: true,
    }]
}]

I would like to run an update command to produce the following. If isBid == true, then I want reason to be "Prediction". Else, reason should be "Result".

[{
    reputationHistory: [{
        isBid: true,
        isResult: false,
        reason: "Prediction",
    }, {
        isBid: false,
        isResult: true,
        reason: "Result",
    }]
}, {
    reputationHistory: [{
        isBid: true,
        isResult: false,
        reason: "Prediction",
    }, {
        isBid: false,
        isResult: true,
        reason: "Result",
    }]
}]

Below is my attempt:

db.users.update(
    {},
    {
        $set: {
            "reputationHistory.$[].reason": {
                $cond: { if: { "reputationHistory.$[].isBid": true }, then: "Prediction", else: "Result" }
            }   
        }

    },
    { multi: true }
)

Below is the error I receive:

WriteResult({
    "nMatched" : 0,
    "nUpserted" : 0,
    "nModified" : 0,
    "writeError" : {
        "code" : 16837,
        "errmsg" : "cannot use the part (reputationHistory of reputationHistory.$[].reason) to traverse the element ({reputationHistory: [ { isBid: true, isResult: false, _id: ObjectId('5e55042c097bca0004647e18') } ]})"
    }
})

How can I edit my MongoDB query to achieve my goal?

mongodb database
1 Answers

MongoDB 4.2 allows you to run Updates with Aggregation Pipeline, try:

db.users.updateMany({}, [ 
    { 
        $addFields: { 
            reputationHistory: { 
                $map: { 
                    input: "$reputationHistory",
                    in: {
                        $mergeObjects: [
                            "$$this",
                            { reason: { $cond: { if: { $eq: [ "$$this.isBid", true ] }, then: "Prediction", else: "Result" } } }
                        ]
                    }
                } 
            } 
        } 
    } 
])

If you can't upgrate the you can use $out which will effectively replace your existing collection with aggregation result:

db.users.aggregate([ 
    { 
        $addFields: { 
            reputationHistory: { 
                $map: { 
                    input: "$reputationHistory",
                    in: { 
                        reason: { $cond: { if: { $eq: [ "$$this.isBid", true ] }, then: "Prediction", else: "Result" } },
                        isBid: "$$this.isBid",
                        isResult: "$$this.isResult"
                    }
                } 
            } 
        } 
    },
    {
        $out: "users"
    } 
])
user_62389770
Related