How to modify an element in an embedded list in dynamodb with document client

Question

I have a DynamoDB table where each item in the table has an array of comments as an attribute. My schema looks something like this:

{
  "id": "abc",
  "name": "This is an item"
  "comments": [
    "commentId": "abcdefg",
    "commentAuthor": "Alice",
    "commentDetails": "This item is my favourite!"
  ]
}

I want to be able to edit an individual comment by its commentId, but it's not clear how to write a DynamoDB expression for this update (I'm using DocumentClient).

How can we update an entry in an embedded array in Dynamo? Is it possible to update by array index, or by a query expression?

score 8 · Answer 1 · answered Feb 13 '18 at 19:09

8

I think there should be some modification to your data model.You should have used dynamodb map for this just make your commentId as a key to access each comment

 {
  "id": "abc",
  "name": "This is an item"
  "comments": {
    "abcdefg":{
      "commentAuthor": "Alice",
      "commentDetails": "This item is my favourite!"
    }
  }
}

Then the query would look like this and you must know two things to update the comment 1 is Item id and 2 is comment id

var params = {
  TableName: Tablename,
  Key: {id: "abc"},
  UpdateExpression:"set comments.#abcdefg.commentDetails=:val1",
  ExpressionAttributeNames: {
    "#abcdefg":"abcdefg"
  },
  ExpressionAttributeValues:{
    ":val1":"New comment text"
  }
}

 db.update(param,callback);

Please Let me know and pardon me,explain your question if i misunderstood as i did not have enough points to clear my doubts in comment.

answered Feb 13 '18 at 19:09

saurabh yadav

153
1
7

Thanks for the answer. This seems viable, and is one option I considered, but maps don't really offer the same functionality as arrays. For example, the ordering is important for when comments were registered, so pushing new items onto an array makes sense. Still, there are ways that you could probably hack around that. – Jim Feb 14 '18 at 01:34
2

AFAIK if you want to do it for arrays and not maps you have to pull down the entire array, make the changes you want, and replace the array with the new version of it. From a database design standpoint, if you are stuck with Dynamo, it may make more sense to fake a relation where you just store the comment_id in an array and store the comments in their own table. Then in your code stitch the two together before sending to the client. – jaredkwright Feb 28 '18 at 21:01
I think the best way to go is to use maps, as suggested by @saurabh yadav and to use Secondary Indexes, to keep the comments sorted, as discussed here https://stackoverflow.com/questions/14836600/querying-dynamodb-by-date – diogenesgg Mar 01 '18 at 14:30
@jaredkwright "AFAIK if you want to do it for arrays and not maps you have to pull down the entire array, make the changes you want, and replace the array with the new version of it" - the query demonstrated in my answer contradicts that. – גלעד ברקן Mar 03 '18 at 16:30

score 4 · Answer 2 · edited Mar 05 '18 at 12:45

Personally, I would not put items I want to edit or access frequently this far away from the primary key. Remember DynamoDB is basically a key-value store, so whenever you want to access stuff it's always better to have those items exposed through primary keys.

So what could you do about this, in my opinion, the best way is to slightly alter your data model. So I would create a table with a primary key ID and a sort key of the item type. In your case, you would now have two item types, "Item", and "comment_[commentID]". Each comment could have the author and details fields as its attributes so something like this:

- [ID    - ITEMTYPE]         - NAME
- "abc"  - "item"            - "This is an item"
- [ID    - ITEMTYPE]         - AUTHOR  - DETAILS
- "abc"  - "comment_abcdefg" - "Alice" - "This item is my favourite!"

Now if you want to get all comments for an item you simply query for the item ID with the sort key starts with "comment". If you want to edit a comment you could simply get this item by adding the ID to the sort key which would result in an exact match. (also this would be VERY fast). There are several ways in which you could make this even more flexible but this should be enough to do what you asked for.

So sorry I just read your comment on the other answer, of course, you can add a sorting number in between the comment and the ID field in the item type sortkey this way the comments would sort automatically in that order(or reverse off course).

גלעד ברקן · Answer 3 · 2018-03-03T01:14:35.070

Is it possible to update by array index...?

Yes, it's quite easy if you know the index. This worked for me in my local localhost:8000/shell/ just now. I assumed that each comment element in the comments list is an object and that you forgot the brackets {} around the comment attributes in your example. Please let me know if I misunderstood or if there's more to your needs.

var params = {
    TableName: 'table_name',
    Key: { // The primary key of the item (a map of attribute name to AttributeValue)

        id: 'abc', //(string | number | boolean | null | Binary)
        // more attributes...
    },
    UpdateExpression: 'SET comments[1] = :value', // String representation of the update to an attribute
        // SET set-action , ... 
        // REMOVE remove-action , ...  (for document support)
        // ADD add-action , ... 
        // DELETE delete-action , ...  (previous DELETE equivalent)
    ExpressionAttributeValues: { // a map of substitutions for all attribute values
        ':value': {
            commentId: 'abcdefg1',
            commentAuthor: 'Alice',
            commentDetails: 'changed'
         }
    },
    ReturnValues: 'NONE', // optional (NONE | ALL_OLD | UPDATED_OLD | ALL_NEW | UPDATED_NEW)
    ReturnConsumedCapacity: 'NONE', // optional (NONE | TOTAL | INDEXES)
    ReturnItemCollectionMetrics: 'NONE', // optional (NONE | SIZE)
};
docClient.update(params, function(err, data) {
    if (err) ppJson(err); // an error occurred
    else ppJson(data); // successful response
});

Output:

score 1 · Answer 4 · answered Jul 28 '20 at 05:32

This is the way I used to deactivate users from a UsersCategoryTable:

        const userIdx = users.map(user => user.M.id.S).indexOf(userId)

        const deactivateUserParams = {
            TableName: USERS_CATEGORY_TABLE_DEV,
            Key: {id: {S: catId}},
            UpdateExpression: `SET updatedAt = :updated_at, #users[${userIdx}].#status = :new_status`,
            ExpressionAttributeNames: {
                "#users": 'users',
                "#status": "status"
            },
            ExpressionAttributeValues: {
                ":new_status": {"S": "INACTIVE"},
                ":updated_at": {"S": new Date().toISOString()}
            },
            ReturnValues: "UPDATED_NEW",
        }

How to modify an element in an embedded list in dynamodb with document client

4 Answers4

Output: