UpdateExpression: Two document paths overlap with each other; must remove or rewrite one of these path

Question

I have been using this query for a few months, and I don't remember seeing this error before.

I am trying to run the update query below, through a DocumentClient from aws-sdk v2 for node.js (some fields omitted for clarity):

```
{
  UpdateExpression:
    'SET #by = :by, #integrations[1] = :update1, #integrations = list_append(#integrations, :added)',
  ConditionExpression: '#integrations[1].#scope = :condition1',
  ExpressionAttributeValues: {
    ':by': 'system',
    ':condition1': 'ScopeAlpha',
    ':update1': {
      scope: 'ScopeAlpha',
      properties: {
        some_uuid: 'c94aea21-b2a5-465a-8c72-4ae43a40622e',
        some_other_uuid: 'f73f6ebb-a073-40fd-a0a6-43522ed9fe45'
      }
    },
    ':added': [
      {
        scope: 'ScopeBeta',
        properties: {
          some_uuid: 'c94aea21-b2a5-465a-8c72-4ae43a40622e',
          some_other_uuid: 'baac4f71-5202-463b-8336-b82a27f11356'
        }
      }
    ]
  },
  ExpressionAttributeNames: {
    '#by': 'updated_by',
    '#integrations': 'integrations',
    '#scope': 'scope'
  }
}
```

My goals is to update an existing entry of a list, and to insert new items to it as well.

I would rather have all this in a single call, as it reduces the update events coming out of the table stream, as I drive a lot of behaviour out of those. It also reduces the likelihood duplications due to double handling.

For some reason, this call now fails with the following error:
```
ValidationException: Invalid UpdateExpression: Two document paths overlap with each other; must remove or rewrite one of these paths; path one: [integrations, [1]], path two: [integrations]
```

I don't see anything wrong with this call, and I've tried updating it slightly in different ways to see if I can get it to work, all to no avail.

Could someone tell me if there is anything that I miss that makes this invalid, or let me know if this should work at all or not?

Thank you.

Accepted Answer

It's always been this way. It's hard from the outside to reliably answer "why" questions.

This StackOverflow post shows a technique to assign and append in one call: https://stackoverflow.com/questions/66534235/invalid-updateexpression-two-document-paths-overlap-with-each-other-must-remov

Answer

Hello @MassimilianoA,

I understand that I use it twice, and the message is clear on what must happen.

My question is WHY this is the case? I don't see how these operations would clash. One operation is updating existing items, and the other is merely adding new items, that were never there in the 1st place. Why would DynamoDB stop me from doing that? And I have been using the specific code that generates this for a while now, and I find it hard to believe that this specific situation did not happen before.

I have also tried `REMOVE` followed by a `SET list_append`, and that also does not work. In that case, it is not operations over the same field in the same `SET` statement, but it fails the same way. Seems like this is a blanket validation rule applied over the expression, disregarding the fact that the field is a list.

The use case is as I mentioned in my question: I do not want multiple update events in my table stream, if I can avoid those. And it would reduces the likelihood of issues due to multiple concurrent updates.

So, summarizing:
+ Did this change, or was it always like this?
+ Why shouldn't it be supported? I understand this limitation for other field types, but not for lists.
+ Should I expect a fix, or should I just go ahead and split the operations?

Answer

The problem is in the SET expression, where you are referring twice to the same attribute `#integrations`. You need to remove one of `#integrations[1] = :update1` or `#integrations = list_append(...)` to make the expression valid.

This means that you cannot update an item in the list and append new items in a single operations.

Sharing some additional information about the specific use case (ie the need to update the 2nd element of the list at the same time that you are adding new elements) might help in providing alternative solutions to achieve your goal of minimizing the number of operations on the table.

UpdateExpression: Two document paths overlap with each other; must remove or rewrite one of these path

Relevant content