I have an AWS DynamoDB table that is populated via a Lambda script triggered by a web form.
The table ends up with Attribute Names like "Full Name" and "Phone Number".
From the aws CLI in PowerShell I can run:
aws dynamodb scan --table-name cc_career --filter-expression 'ID = :t' --expression-attribute-values '{\":t\":{\"N\":\"12\"}}'
and it will return expected values (Attribute Name = ID, value = 12).
But if I want to filter on the attribute "Full Name", for example:
aws dynamodb scan --table-name cc_career --filter-expression 'Full Name = :t' --expression-attribute-values '{\":t\":{\"S\":\"Sherman Horton\"}}'
I get
An error occurred (ValidationException) when calling the Scan operation: Invalid FilterExpression: Syntax error; token: "Name", near: "Full Name ="
How does one properly escape or specify an Attribute Name that contains a space?
I read up on using "expression attribute names" from the docs. But even this example:
aws dynamodb scan --table-name cc_career --return-consumed-capacity "TOTAL" --projection-expression "#fn,#dt" --expression-attribute-names '{\"#fn\":\"Email\",\"#dt\":\"Full Name\"}'
will execute without error BUT not return the "Full Name" data.
I did a pretty thorough 'net search on this topic but found nothing. Surely it's a common use case!
CodePudding user response:
You are right about using an expression attribute names
If an attribute name begins with a number, contains a space or contains a reserved word, you must use an expression attribute name to replace that attribute's name in the expression.
aws dynamodb scan --table-name cc_career --return-consumed-capacity "TOTAL" --projection-expression "#fn,#dt" --expression-attribute-names '{\"#fn\":\"Email\",\"#dt\":\"Full Name\"}'
The problem here is that you are missing filter expression
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Scan.html#Scan.FilterExpression
Considering the table design with ID
as PK and Full Name
and Phone Number
using below command I was able to scan Data based on Full Name
aws dynamodb scan --table-name stack_overflow --filter-expression "#fn = :t" --expression-attribute-values '{":t":{"S":"Jatin Mehrotra"}}' --expression-attribute-name '{"#fn":"Full Name"}
My results after running above command
{
"Items": [
{
"ID": {
"N": "1"
},
"Full Name": {
"S": "Jatin Mehrotra"
},
"Phone Number": {
"S": "123456789"
}
}
],
"Count": 1,
"ScannedCount": 1,
"ConsumedCapacity": null
}