How to set a DynamoDB Map property value, when the map doesn't exist yet

Question

How do you "upsert" a property to a DynamoDB row. E.g. SET address.state = "MA" for some item, when address does not yet exist?

I feel like I'm having a chicken-and-egg problem because DynamoDB doesn't let you define a sloppy schema in advance.

If address DID already exist on that item, of type M (for Map), the internet tells me I could issue an UpdateExpression like:

SET #address.#state = :value

with #address, #state, and :value appropriately mapped to address, state, and MA, respectively.

But if the address property does not already exist, this gives an error:

''' ValidationException: The document path provided in the update expression is invalid for update '''

So.. it appears I either need to:

1. Figure out a way to "upsert" address.state (e.g., SET address = {}; SET address.state = 'MA' in a single command)

or

2. Issue three (!!!) roundtrips in which I try it, SET address = {}; on failure, and then try it again.

If the latter.... how do I set a blank map?!?

Ugh.. I like Dynamo, but unless I'm missing something obvious this is a bit crazy..

Answer 1

You can do it with two round trips, the first conditionally sets an empty map for address if it doesn't already exist, and the second sets the state:

db.update({
    UpdateExpression: 'SET #a = :value',
    ConditionExpression: 'attribute_not_exists(#a)',
    ExpressionAttributeValues: {
        ":value": {},
    },
    ExpressionAttributeNames: {
        '#a': 'address'
    }
}, ...);

Then:

db.update({
    UpdateExpression: 'SET #a.#b = :v',
    ExpressionAttributeNames: {
        '#a': 'address',
        '#b': 'state'
    },
    ExpressionAttributeValues: {
        ':v': 'whatever'
    }
}, ...);

Answer 2

You cannot set nested attributes if the parent document does not exist. Since address does not exist you cannot set the attribute province inside it. You can achieve your goal if you set address to an empty map when you create the item. Then, you can use the following parameters to condition an update on an attribute address.province not existing yet.

var params = {
    TableName: 'Image',
    Key: {
        Id: 'dynamodb.png'
    },
    UpdateExpression: 'SET address.province = :ma',
    ConditionExpression: 'attribute_not_exists(address.province)',
    ExpressionAttributeValues: {
        ':ma': 'MA'
    },
    ReturnValues: 'ALL_NEW'
};
docClient.update(params, function(err, data) {
    if (err) ppJson(err); // an error occurred
    else ppJson(data); // successful response
});

By the way, I had to replace state with province as state is a reserved word.

Answer 3

Another totally different method is to simply create the address node when creating the parent document in the first place. For example assuming you have a hash key of id, you might do:

db.put({
    Item: {
        id: 42,
        address: {}
    }
}, ...);

This will allow you to simply set the address.state value as the address map already exists:

db.update({
    UpdateExpression: 'SET #a.#b = :v',
    AttributeExpressionNames: {
        '#a': 'address',
        '#b': 'state'
    },
    AttributeExpressionValues: {
        ':v': 'whatever'
    }
}, ...);

Answer 4

Some kotlin code to do this recursively regardless how deep it goes. It sets existence of parent paths as condition and if condition check fails, recursively creates those paths first. It has to be in the library's package so it can access those package private fields/classes.

package com.amazonaws.services.dynamodbv2.xspec

import com.amazonaws.services.dynamodbv2.document.Table
import com.amazonaws.services.dynamodbv2.model.ConditionalCheckFailedException
import com.amazonaws.services.dynamodbv2.xspec.ExpressionSpecBuilder.attribute_exists

fun Table.updateItemByPaths(hashKeyName: String, hashKeyValue: Any, updateActions: List<UpdateAction>) {
    val parentPaths = updateActions.map { it.pathOperand.path.parent() }
            .filter { it.isNotEmpty() }
            .toSet()    // to remove duplicates
    try {
        val builder = ExpressionSpecBuilder()
        updateActions.forEach { builder.addUpdate(it) }
        if (parentPaths.isNotEmpty()) {
            var condition: Condition = ComparatorCondition("=", LiteralOperand(true), LiteralOperand(true))
            parentPaths.forEach { condition = condition.and(attribute_exists<Any>(it)) }
            builder.withCondition(condition)
        }
        this.updateItem(hashKeyName, hashKeyValue, builder.buildForUpdate())
    } catch (e: ConditionalCheckFailedException) {
        this.updateItemByPaths(hashKeyName, hashKeyValue, parentPaths.map { M(it).set(mapOf<String, Any>()) })
        this.updateItemByPaths(hashKeyName, hashKeyValue, updateActions)
    }
}

private fun String.parent() = this.substringBeforeLast('.', "")

Answer 5

Here is a helper function I wrote in Typescript that works for this a single level of nesting using a recursive method.

I refer to the top-level attribute as a column.

//usage
await setKeyInColumn('customerA', 'address', 'state', "MA")

  // Updates a map value to hold a new key value pair. It will create a top-level address if it doesn't exist.
    
    static async setKeyInColumn(primaryKeyValue: string, colName: string, key: string, value: any, _doNotCreateColumn?:boolean) {

        const obj = {};
        obj[key] = value; // creates a nested value like {address:value}


        // Some conditions depending on whether the column already exists or not

        const ConditionExpression = _doNotCreateColumn ? undefined:`attribute_not_exists(${colName})`
        const AttributeValue = _doNotCreateColumn? value : obj;
        const UpdateExpression = _doNotCreateColumn? `SET ${colName}.${key} = :keyval `: `SET ${colName} = :keyval ` ;


        try{
            const updateParams = {
                TableName: TABLE_NAME,
                Key: {key:primaryKeyValue},
                UpdateExpression,
                ExpressionAttributeValues: {
                    ":keyval": AttributeValue
                },
                ConditionExpression,
                ReturnValues: "ALL_NEW",
            }
            const resp = await docClient.update(updateParams).promise()

            if (resp && resp[colName]) {
                return resp[colName];
            }
            
        }catch(ex){
            //if the column already exists, then rerun and do not create it
            if(ex.code === 'ConditionalCheckFailedException'){
               return this.setKeyInColumn(primaryKeyValue,colName,key, value, true)
            }
            console.log("Failed to Update Column in DynamoDB")
            console.log(ex);
            return undefined
        }
    
    }

Answer 6

I've got quite similar situation. I can think of only a one way to do this in 1 query/atomically.

Extract map values to top level attributes.

Example

Given I have this post item in DynamoDB:

{
  "PK": "123",
  "SK": "post",
  "title": "Hello World!"
}

And I want to later add an analytics entry to same partition:

{
   "PK": "123",
   "SK": "analytics#december",
   "views": {
      // <day of month>: <views>
      "1": "12",
      "2": "457463",
      // etc

   }
}

Like in your case, it's not possible to increment/decrement views days counters in single query if analytics item nor views map might not exist (could be later feature or don't want to put empty items).

Proposed solution:

{
   "PK": "123",
   "SK": "analytics#december",
   // <day of month>: <views>
   "1": "12", // or "day1" if "1" seems too generic
   "2": "457463",
   // etc
   }
}

Then you could do something like this (increment +1 example):

{
  UpdateExpression: "SET #day = if_not_exists(#day, 0) + 1",
  AttributeExpressionNames: {
    '#day': "1"
  }
}

• if day attribute value doesn't exist, set default value to 0
• if item in database doesn't exist, update API adds a new one