Struggling to make a complex query for NO-SQL

user_9909914 0
Viewed 30

I have an object in MongoDB and trying to write a query that finds the document by company_id then goes through the purchases array and update paid to true in all record that matches with user_id, without overwriting the rest of the data.

{
    _id: 27834682,
    company_id: 27,
    purchases: [
        {
            paid: false,
            user_id: 19,
            items: [{...}, {...}],
            price: 1085
        },
        ...    
    ]
}

I was trying out

Model.updateMany({company_id: company_id}, {"purchases.$[]": {$set: {paid: true}}})

but it removed the other data in the object and could not even filtered by user_id.

What would be the correct approach for this query?

mongodb nosql
1 Answers

When updating an array field, you can specify arrayFilters that determine which array elements to update.

Quick Fixes,

  • define condition user_id: 19 in arrayFilters, syntax <user define key>.<identifier key>,

  • element.user_id, element is user define key, you can set any of the key name, user_id is identifier field inside array

  • update operation start with $set

  • set value for array element using $[key], here key is user define key that we have defined in arrayFilters, it looks purchases.$[element].paid

Model.updateMany(
    { 
        company_id: company_id
    },
    {
        $set: {
            "purchases.$[element].paid": true
        }
    },
    { 
        arrayFilters: [ 
            { 
                "element.user_id": 19
            }
        ]
    }
)
user_89871280
Related