DynamoDB - Why can't I use an "_" as a prefix in my key condition expression?

Question

I am using the AWS DynamoDB DocumentClient to query my DynamoDB tables. In my tables I have a property called "_id" that holds a unique ID for each entry. When I try to query for a record based on this ID, I end up receiving an error that says: "Invalid KeyConditionExpression: Syntax error; token: \"_\", near: \"_id\"". The code that makes this query is below.

    function findById(id) {

     //Build query
    var params = {};
    params.TableName = "recordDev";
    params.IndexName = "_id";
    params.KeyConditionExpression = '_id = :id';
    params.ExpressionAttributeValues = {
      ':id': id
    };

    return DynamoDB
        .query(params).promise()
        .then(function(records) {
          return records.Items[0];
        })
        .catch(function(error) {
          return Promise.reject(error);
        });
   };

Is there something that I am missing with regards to using an "_" when building the query params for DynamoDB? I have tried looking around for similar errors, but have been unable to find any that are like my scenario.

Answer 1

Can you try using the ExpressionAttributeNames param?

var params = {};
params.TableName = "recordDev";
params.IndexName = "_id";
params.KeyConditionExpression = '#id = :id';
params.ExpressionAttributeValues = {
  ':id': id
};
params.ExpressionAttributeNames: {
        "#id":"_id"
};

Answer 2

The same problem also occurs when you have "-" in property name. For example, I had "id-test" as the property name. I got the same error:

An error occurred (ValidationException) when calling the Query operation: Invalid KeyConditionExpression: Syntax error; token: "-", near: "teamid-testid"

The solution suggested by @gkatzioura works in this case too. Here is my implementation using Python boto3 conventions:

params = {}

params['TableName'] = "myTable"
params['ProjectionExpression']="idStatus"
params['KeyConditionExpression'] = 'scanid = :val1 and #idtest = :val2'
params['ExpressionAttributeValues'] = {":val1":{"S": scanid}, ":val2": {"S": id_test}}
params['ExpressionAttributeNames'] = {"#idtestid": "id-test"}

Answer 3

You can try for Java case this code:

HashMap<String, AttributeValue> itemValues = new HashMap<String, AttributeValue>();
itemValues.put(":id", AttributeValue.builder().s(key).build());

QueryRequest queryRequest = QueryRequest.builder().tableName("recordDev")
            .keyConditionExpression("_id = :id").expressionAttributeValues(itemValues).build();

QueryResponse queryResponse = dynamoDBClient.query(queryRequest);

Works for me.