Scan Function in DynamoDB with reserved keyword as FilterExpression NodeJS

Question

My scan function :

var tableName = 'faasos_orders',
    filterExp = 'status = :delivered OR status = :void OR status = :bad',
    projectionValues = '',
    expressionAttr = {};    
    expressionAttr[":delivered"] = "delivered";
    expressionAttr[":bad"] = "bad";
    expressionAttr[":void"] = "void"; 
    limit = 10;
  dynamoConnector.getItemUsingScan(tableName, filterExp, projectionValues, expressionAttr, function (err, data) {  ...........}

Error on running :

    { [ValidationException: Invalid FilterExpression: Attribute name is a reserved keyword; reserved keyword: status]
  message: 'Invalid FilterExpression: Attribute name is a reserved keyword; reserved keyword: status',
  code: 'ValidationException',
  time: Mon Apr 18 2016 21:57:30 GMT+0530 (IST),
  requestId: 'AV6QFHM7SPQT1QR3D4OO81ED4FVV4KQNSO5AEMVJF66Q9ASUAAJG',
  statusCode: 400,
  retryable: false,
  retryDelay: 0 }

Now I do get the point I am trying to use a reserved keyword in th e filterExpression which is illegal. But if I run the same function through aws gui it returns data beautifully (check image for details): Scan function on status through gui

So the question is how do I add the filter expression through node without having to change the key name ???

score 126 · Accepted Answer · edited Aug 13 '20 at 11:44

126

Solved :

There are two parameters taken by aws-sdk :

Expression Attribute Name

Expression Attribute Value

both provide the functionality of replacing placeholders used in the attributes list. Here by Attributes it is a bit ambiguous, where I got confused. The wizards over at aws mean both the key and value when they use the term attribute.

So in a case where you want to use a reserved key word as a key attribute use the Expression Attribute Name parameter with #(pound) to denote the placeholder.

Similarly where you want to use placeholders for value attribute use the Expression Attribute Value parameter with :(colon) to denote the placeholder.

So finally my code (working) looks like this :

var param = {
  TableName: "faasos_orders",
  FilterExpression: "#order_status = :delivered OR #order_status = :void OR #order_status = :bad",
  ExpressionAttributeValues: {
    ":delivered": "delivered",
    ":void": "void",
    ":bad": "bad"
  },
  ExpressionAttributeNames: {
    "#order_status": "status"
  }
};  
  dynamodb.scan(param, function (err, data) {....});

edited Aug 13 '20 at 11:44

cssBlaster21895

3,670
20
33

answered Apr 19 '16 at 08:15

Saleem Ahmed

2,719
2
18
31

1

Thank you for your effort. Had a similar error. :-) – kometen May 01 '16 at 10:54
1

Yay. Amazons Documentation is just so awesome you know. Though found this their main guide for DynamoDB : [Dev Guide Latest Amazon dynamodb](http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Introduction.html) @kometen – Saleem Ahmed May 08 '16 at 06:49
1

This also works in C# (with a slight syntax change) and presumably all other languages supported by the aws sdks. – Elliot Blackburn Sep 02 '19 at 13:18
Here is the c# syntax @ElliotBlackburn mentioned https://docs.aws.amazon.com/sdk-for-net/v2/developer-guide/dynamodb-expressions.html – Craig.C Sep 10 '20 at 12:45

score 3 · Answer 2 · answered Apr 18 '16 at 16:14

3

:status is a placeholder in your expression that you aren't providing a value for. See how you are providing values for your other placeholders here:

expressionAttr[":delivered"] = "delivered";
expressionAttr[":bad"] = "bad";
expressionAttr[":void"] = "void"

You need to do the same for the :status placeholder. I don't see anything about a reserved word in the error message, so I'm not sure why you think that's the cause of the error. The error very specifically states that you aren't providing a value for the :status placeholder.

answered Apr 18 '16 at 16:14

Mark B

183,023
24
297
295

Please see the edits . had been toying with the placeholder as explained here http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ExpressionPlaceholders.html#ExpressionAttributeNames Though neither #status nor :status seems to work , { [ValidationException: ExpressionAttributeValues contains invalid key: Syntax error; key: "#status"] And with :status the expression though runs smoothly but returns no data – Saleem Ahmed Apr 18 '16 at 16:33
You have to use the placeholder syntax like in your original question text, but then you also need to provide the value for that placeholder: `expressionAttr[":status"] = "status"` – Mark B Apr 18 '16 at 16:34
That doesnt match the condition mate. No value is returned. Though I have values in the db as shown on image. The aws doc says to use # for expression attribute names and : for expression attribute values. – Saleem Ahmed Apr 18 '16 at 16:48
Then use `#` and see if it works? The fact is you have to provide a value for any placeholders you put in the query, which is what the error message is complaining about. – Mark B Apr 18 '16 at 16:55
Old post but it seems to need some clarification. The error message specifically mention an error about a reserved keyword! "Status" is a reserved keyword per documentation (https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ReservedWords.html) . That's why you needed to use the ExpressionAttributeNames to be still be able to use the attribute "status" in the filter as below. – Steve S. Oct 12 '21 at 22:33

Scan Function in DynamoDB with reserved keyword as FilterExpression NodeJS

2 Answers2