Deleting records from a DynamoDB table where the TTL value has been set incorrectly

Question

I've inherited a service where the TTL value for the DynamoDB records is in the following format

2022-11-03T10:35:42.920Z

Does anyone know how I can run a query to delete all the records with a TTL value that is in the above format rather than Unix epoch time format?

Answer 1

If you want to find out which items are requiring deletion you can return items which have TTL set as a String:

aws dynamodb scan \
    --table-name MyTable \
    --filter-expression "attribute_type(#ttl, :string)" \
    --expression-attribute-names '{"#ttl": "TTL"}' \
    --expression-attribute-values '{":string": {"S":"S"}}'

Now, you must update those values for each item returned. If its a small dataset then you can easily iterate using the CLI and update each item. This would look something like this:

TABLE_NAME="MyTable"

ITEMS=$(aws dynamodb scan \
    --table-name $TABLE_NAME \
    --filter-expression "attribute_type(ttl, :type)" \
    --expression-attribute-values '{":type":{"S":"S"}}' \
    --query "Items" \
    --output json)

echo $ITEMS | jq -c '.[]' | while read item; do
    # Extract the primary key(s) of the item
    KEY=$(echo $item | jq '{YourPrimaryKeyAttribute: .YourPrimaryKeyAttribute}')

    aws dynamodb update-item \
        --table-name $TABLE_NAME \
        --key "$KEY" \
        --update-expression "SET #ttl = :ttl" \
        --expression-attribute-names '{"#ttl":"ttl"}' \
        --expression-attribute-values '{":ttl":{"N":"new_ttl_value"}}'
done

There is also alternatives for larger data sets, such as Step Functions Distributed Map.