CODEWITHSUNDEEP
amazon-web-servicesamazon-dynamodbdynamodb-queries
Stephan Prätsch
Stephan Prätsch

Reputation: 119

DynamoDB: Query a secondary index with prefix (begins_with) only

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

Upvotes: 5

Views: 6057

Answers (2)

Sebastialonso
Sebastialonso

Reputation: 1460

Late to the party, but for anyone reading this: if you're querying by partition key (GSI or not) you must ask for equality. If you have a sort key, you can then apply some fun functions to its condition. Reference

What OP did to solve their problem is effectively that: it turned the primary key to a equality/hashable comparison. Which is the DynamoDB way of solving issues.

Upvotes: 5

Stephan Prätsch
Stephan Prätsch

Reputation: 119

I solved it by adding a new field that holds only the first digit of the AlbumTitle named AlbumTitleForHash and this will be the hash key for the GSI.

Then I can query for AlbumTitleHash=<first digit of query> and AlbumTitle=<query>

Upvotes: 1

Related Questions