I want to a DynamoDB query for a string that's not a part of the primary key and I only have this string.
As an example I have
Music
Artist: String, PrimaryKey(HASH)
SongTitle: String, PrimaryKey(RANGE)
AlbumTitle: String, GlobalSecondaryKey(probably RANGE)
and I want to query "all Music items that have AlbumTitle starts with 'Some'".
I tried this with aws cli
create table (still with HASH for AlbumTitle)
aws dynamodb create-table \
--table-name Music \
--attribute-definitions \
AttributeName=Artist,AttributeType=S \
AttributeName=SongTitle,AttributeType=S \
AttributeName=AlbumTitle,AttributeType=S \
--key-schema \
AttributeName=Artist,KeyType=HASH \
AttributeName=SongTitle,KeyType=RANGE \
--provisioned-throughput \
ReadCapacityUnits=10,WriteCapacityUnits=5 \
--global-secondary-indexes \
"[
{
\"IndexName\": \"AlbumTitle-Index\",
\"KeySchema\": [{\"AttributeName\":\"AlbumTitle\",\"KeyType\":\"HASH\"}],
\"Projection\":{ \"ProjectionType\":\"ALL\" },
\"ProvisionedThroughput\":{
\"ReadCapacityUnits\": 1,
\"WriteCapacityUnits\": 1
}
}
]"
wait until
aws dynamodb describe-table --table-name Music | grep IndexStatus
responds
"IndexStatus": "ACTIVE",
put some items
aws dynamodb put-item \
--table-name Music \
--item '{"Artist": {"S": "No One You Know"}, "SongTitle": {"S": "Call Me Today"}, "AlbumTitle": {"S": "Somewhat Famous"}, "Awards": {"N": "1"}}'
aws dynamodb put-item \
--table-name Music \
--item '{"Artist": {"S": "No One You Know"}, "SongTitle": {"S": "Call Me Tomorrow"}, "AlbumTitle": {"S": "Something Famous"}, "Awards": {"N": "1"}}'
aws dynamodb put-item \
--table-name Music \
--item '{"Artist": {"S": "Acme Band"}, "SongTitle": {"S": "Happy Day"}, "AlbumTitle": {"S": "Songs About Life"}, "Awards": {"N": "10"} }'
query with equal (works)
aws dynamodb query \
--table-name Music \
--index-name AlbumTitle-Index \
--key-condition-expression "AlbumTitle = :name" \
--expression-attribute-values '{":name":{"S":"Somewhat Famous"}}'
query with begins_with (does not work)
aws dynamodb query \
--table-name Music \
--index-name AlbumTitle-Index \
--key-condition-expression "begins_with(AlbumTitle, :name)" \
--expression-attribute-values '{":name":{"S":"Some"}}'
results in
An error occurred (ValidationException) when calling the Query operation: Query key condition not supported
Ok. That does not work, because I created the index AlbumTitle-Index with AlbumTitle(HASH). Hence I only can query equals.
problem / question
What do I need to change that I can perform a prefix search? I only find begins_with and everywhere it's combined with a HASH key as its first part. I don't have anything but the string that should be matched as a prefix in AlbumTitle.
I cannot simply replace HASH with RANGE in the AlbumTutle-Index like
--global-secondary-indexes \
"[
{
\"IndexName\": \"AlbumTitle-Index\",
\"KeySchema\": [{\"AttributeName\":\"AlbumTitle\",\"KeyType\":\"RANGE\"}],
...
because of
An error occurred (ValidationException) when calling the CreateTable operation: Invalid KeySchema: The first KeySchemaElement is not a HASH key type
