DynamoDB syntax for nested sub-tables
posted 2020.05.02 by Clark Wilkins, Simplexable

I ran into a lot of difficulty with syntax and timeout errors today while setting up a DynamoDB query to update a deeply nested sub-table. Here's the format and the solution.

  Users (table)
groupId (primary key)
users (text label, map)
userId (32-character ID, map)
accounts (text label, map)
companyId (32-character ID, map)
serviceName (text label, map)
attribute n

To specify this in AWS Lambda, runtime Node.js 12.x, you have to do this:

  const loc = "#users.#userId.accounts.#companyId.#serviceName";

var params = {
TableName: "Users",
Key: {"groupId": groupId},
"set " +
loc +
".active = :active, " +
loc +
".email = :email, " +
loc +
".#level = :level, " +
loc +
".#privileges.canManageClients = :clients, " +
loc +
".#privileges.canManageUsers = :users, " +
loc +
".server = :server",
ExpressionAttributeValues: {
":active": userRecord.active,
":clients": userRecord.manageClients,
":email": userRecord.email,
":level": userRecord.level,
":server": userRecord.server,
":users": userRecord.manageUsers
ExpressionAttributeNames: {
"#companyId": companyId,
"#level": "level",
"#privileges": "privileges",
"#serviceName": serviceName,
"#userId": userId,
"#users": "users"
ReturnValues: "UPDATED_NEW"

There are two very tricky aspects here. First, look at loc which needs all of the subtables specified as “ExpressionAttributeNames”, separated by the normal periods for map attributes.1

Second, due to the very large list of reserved keywords in DynamoDB2, I had to use a lot of “ExpressionAttributeValues”, but the real trick to know that, external to the UpdateExpression itself, you can't use the ExpressionAttributeValues (denoted with :thisKey) — you have to use ExpressionAttributeNames instead and denote as #thisKey. The errors thrown, including syntax issues or a very general function timeout are not helpful in diagnosing the problem.

I reviewed dozens of pages of official and other documentation, and to now, I have not see this clarified, so here you go. Dynamo has a lot of great aspects, but it's extremely unforgiving and rigid. This is an example that cost me several hours of experimentation to figure out.

  1. We want to reach Users.[this group].users.[this user].accounts.[this company].[this service] to make our changes.
  2. A full list is here.