[SERVER-41382] Update in nested array based on Date queries may not work properly Created: 30/May/19  Updated: 03/Jun/19  Resolved: 03/Jun/19

Status: Closed
Project: Core Server
Component/s: Internal Client
Affects Version/s: 3.6 Required
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Michal Kotra Assignee: Eric Sedor
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-14662 Positional projection queries (and po... Closed
Operating System: ALL
Steps To Reproduce:

Create collection "test", and insert one document into it:

{
{
    "_id" : NumberLong(1),
    "pointsBalances" : [ 
        {
            "period" : "2020",
            "startDate" : ISODate("2020-01-01T00:00:00.000Z"),
            "endDate" : ISODate("2020-12-31T23:59:59.999Z"),
            "value" : 0
        }, 
        {
            "period" : "2019",
            "startDate" : ISODate("2019-01-01T00:00:00.000Z"),
            "endDate" : ISODate("2019-12-31T23:59:59.999Z"),
            "value" : 0
        }
    ]
}

Then please perform update - it should work properly:

db.getCollection('test').update(
  {$and: [ {'_id': 1}, {"pointsBalances.period": '2019'}]},
  {$set: { "pointsBalances.$.value": 100 } }
)

Now lets try to update based on dates:

db.getCollection('test').update(
{ $and: [ {'_id': 1}, {"pointsBalances.startDate": {$lte: ISODate("2019-05-01T00:00:00.000Z")}}, {"pointsBalances.endDate": {$gte: ISODate("2019-05-01T00:00:00.000Z")}}  ]},
  {$set: { "pointsBalances.$.value": 200 } }
)

Second code updates wrong array element.
Please notice that this behavior only occurs if points balances are not sorted. So in this case it will work properly:

{
    "_id" : NumberLong(1),
    "pointsBalances" : [ 
        {
            "period" : "2019",
            "startDate" : ISODate("2019-01-01T00:00:00.000Z"),
            "endDate" : ISODate("2019-12-31T23:59:59.999Z"),
            "value" : 0
        }, 
        {
            "period" : "2020",
            "startDate" : ISODate("2020-01-01T00:00:00.000Z"),
            "endDate" : ISODate("2020-12-31T23:59:59.999Z"),
            "value" : 0
        }
    ]
}

Participants:

 Description   

Update in nested array based on Date queries may notwork properly.



 Comments   
Comment by Eric Sedor [ 03/Jun/19 ]

michal_kotra, I am going to close this as a duplicate of SERVER-14662. but have also opened DOCS-12773 to help clarify this behavior in the future.

Please see Update Embedded Documents Using Multiple Field Matches for how to target specific array elements using $elemMatch. This operation should get you what you are looking for:

db.getCollection('test').update(
{ $and: [ {'_id': 1}, {"pointsBalances": {"$elemMatch":{"startDate": {$lte: ISODate("2019-05-01T00:00:00.000Z")}, "endDate": {$gte: ISODate("2019-05-01T00:00:00.000Z")}}}} ]},
  {$set: { "pointsBalances.$.value": 200 } }
)

Generated at Thu Feb 08 04:57:34 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.