Is it possible to upsert nested fields in DynamoDB?

18
votes

I would like to 'upsert' a document in DynamoDB. That is, I would like to specify a key, and a set of field/value pairs. If no document exists with that key, I want one created with that key and the key/value pairs I specified. If a document exists with that key, I want the fields I specified to be set to the values specified (if those fields did not exist before, then they should be added). Any other, unspecified fields on the existing document should be left alone.

It seems I can do this pretty well with the UpdateItem call, when the field/value pairs I am setting are all top-level fields. If I have nested structures, UpdateItem will work to set the nested fields, as long as the structure exists. In other words, if my existing document has "foo": {}, then I can set "foo.bar": 42 successfully.

However, I don't seem to be able to set "foo.bar": 42 if there is no foo object already (like in the case where there is no document with the specified field at all, and my 'upsert' is behaving as an 'insert'.

I found a discussion on the AWS forums from a few years ago which seems to imply that what I want to do cannot be done, but I'm hoping this has changed recently, or maybe someone knows of a way to do it?

3
amazon-dynamodbupsert
To give you another datapoint, I looked into this in May of 2015 (ish) and the same problem you mentioned still existed. I wasn't able to achieve the goal of nested field upsert, so I had to change my approach to the problem and how I stored the data. - mkobit
@mkobit thanks for your data point; that is the conclusion I came to as well. Since my data had a fixed number of nested levels, I ended up storing it flattened, with fields named like foo_bar, and I added marshalling/unmarshalling code to my DAO layer to translate it. It is a pain, but it seems to work well enough (so far at least) - pkaeding
Same issue one year later. The annoying part is that Dynamo doesn't throw an error, it just doesn't save that portion of the data tree. - jvhang
Anybody got a solution? Please post here. - Vishnu

3 Answers

1
votes

UpdateItem behaves like an "upsert" operation: The item is updated if it exists in the table, but if not, a new item is added (inserted). http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/SQLtoNoSQL.UpdateData.html

1
votes

That ("foo.bar": 42) can be achieved using the below query:

 table.update_item(Key = {'Id' : id},
              UpdateExpression = 'SET foo = :value1',
              ExpressionAttributeValues = {':value1': {'bar' : 42}}
              )

Hope this helps :)

0
votes

I found this UpdateItem limitation (top level vs nested attributes) frustrating as well. Eventually I came across this answer and was able to work around the problem: https://stackoverflow.com/a/43136029/431296

It requires two UpdateItem calls (possibly more depending on level of nesting?). I only needed a single level, so this is how I did it:

  1. Update the item using an attribute_exists condition to create the top level attribute as an empty map if it doesn't already exist. This will work if the entire item is missing or if it exists and has other pre-existing attributes you don't want to lose.

  2. Then do the 2nd level update item to update the nested value. As long as the parent exists (ex: an empty map in my case) it works great.

I got the impression you weren't using python, but here's the python code to accomplish the upsert of a nested attribute in an item like this:

{
  "partition_key": "key",
  "top_level_attribute": {
    "nested_attribute": "value"
  }
}

python boto3 code:

def upsert_nested_item(self, partition_key, top_level_attribute_name, nested_attribute_name, nested_item_value):
    try:
        self.table.update_item(
            Key={'partition_key': partition_key},
            ExpressionAttributeNames={f'#{top_level_attribute_name}': top_level_attribute_name},
            ExpressionAttributeValues={':empty': {}},
            ConditionExpression=f'attribute_not_exists(#{top_level_attribute_name})',
            UpdateExpression=f'SET #{top_level_attribute_name} = :empty',
        )
    except self.DYNAMODB.meta.client.exceptions.ConditionalCheckFailedException:
        pass
    self.table.update_item(
        Key={'partition_key': partition_key},
        ExpressionAttributeNames={
            f'#{top_level_attribute_name}': top_level_attribute_name,
            f'#{nested_attribute_name}': nested_attribute_name
        },
        ExpressionAttributeValues={f':{top_level_attribute_name}': nested_item_value},
        UpdateExpression=f'SET #{top_level_attribute_name}.#{nested_attribute_name} = :{top_level_attribute_name}',
    )