This is the 2nd article in the series for Data Pane 1 (read-write) operations.

In this article, I am going to show you such operations on DynamoDB item collections using Query. Some of the criteria which can be used in the Query operations are - Key condition expression and Filter Expression, with options, such as max items, scan-index-forward, no-scan-index-forward, Count and ScannedCount.

In this article, I am not explaining the terminology and the concepts used in DynamoDB Tables. Please visit my previous articles for this - DynamoDB and its Control Pane Operations - 1 and DynamoDB and its Control Pane Operations - 2.

Item Collections

• They are groups of Items that share a same Partition Key.

• They can only exist in tables that have both a Partition Key and a Sort Key.

• We can read all or part of an Item Collection using the Query.

• The word “query” is generally used to mean “reading data from a database”, but in DynamoDB “query” has a specific meaning: to read all or part of an Item Collection.

Key Condition Expression

• Specify a Key Condition Expression to get the items to be read from the table or index.

• This can be compared to SQL, where we would say this is the part of the WHERE clause that acts on the Partition Key and Sort Key attributes.

• With just the Partition Key value of from a Key Condition Expression, gets ALL the items in the item collection.

• With the Partition and Sort Key Condition from a Key Condition Expression, gets a subset of the rows in item collection.

• Possible sort key conditions are

=, <, <=, >, >=, BETWEEN, and BEGINS_WITH.

Filter Expression

• It removes some items from the Result Set returned by the Query.

• This can be compared in SQL to this is the part of the WHERE clause that acts on the non-Key attributes.

Max items

• If we want to limit the number of items then we use the --max-items option.

Scan index forward

• If we want to order items in ascending order of the sort key, then we would use the --scan-index-forward option.

• This can be compared in SQL to ORDER BY Name ASC LIMIT 1, when --max-items limits to 1.

No Scan index forward

• If we want DynamoDB to order items in descending order of the sort key, then we would use the --no-scan-index-forward option.

• This can be compared in SQL to ORDER BY Name DESC LIMIT 1, when --max-items limits to 1.

ScannedCount and Count

• Counts the items in the results

• In the ScannedCount - When the Key Condition Expression matches 2 items, then it is 2.

• In the Count - When the Filter Expression reduces the result set to 1 item, then it is 1.

• If you don't use a filter expression, ScannedCount and Count have the same value.

Let’s get started!

Please visit my GitHub Repository for DynamoDB articles on various topics being updated on constant basis.

Pre-requisites:

• AWS user account with admin access, not a root account.
• Cloud9 IDE with AWS CLI.

Objectives:

1. Create a DynamoDB table

2. Add 3 items to the table

3. Query the table with Key Condition Expression with just the Partition Key

4. Query the table with Key Condition Expression with Partition Key and Sort key, using "=" option

5. Query the table to limit the number of items with Max items and Scan index forward

6. Query the table to limit the number of items with Max items and Scan index forward

7. Query the table to limit the number of items with Max items and No Scan index forward

8. Query the table to count the items in the result set

Resources Used:

Amazon DynamoDB Developer Guide

Query cli

Steps for implementation to this project:

• Copy the and paste the code in your AWS Cloud9 command prompt.

1. Create a DynamoDB table

aws dynamodb create-table \
    --table-name Movies \
    --attribute-definitions \
        AttributeName=Title,AttributeType=S \
        AttributeName=Review,AttributeType=S \
    --key-schema \
        AttributeName=Title,KeyType=HASH \
        AttributeName=Review,KeyType=RANGE \
    --provisioned-throughput \
        ReadCapacityUnits=10,WriteCapacityUnits=5
aws dynamodb wait table-exists --table-name Movies

2. Add 3 items to the table

aws dynamodb put-item \
    --table-name Movies \
    --item '{
           "Title": {"S": "Abhimaan"},
           "Review": {"S": "Excellent"},
           "Actor": {"S": "Big B"},
           "PostedBy": {"S": "User A"},
           "Songs": {"L": [ {"S": "Song1"},
                            {"S": "Song2"}
                          ]
                    }
            }'
aws dynamodb put-item \
    --table-name Movies \
    --item '{
           "Title": {"S": "Abhimaan"},
           "Review": {"S": "Terrific"},
           "Actor": {"S": "Jaya"},
           "PostedBy": {"S": "User B"},
           "Songs": {"L": [ {"S": "Song1"},
                            {"S": "Song2"}
                          ]
                    }
            }'
aws dynamodb put-item \
    --table-name Movies \
    --item '{
           "Title": {"S": "Abhimaan"},
           "Review": {"S": "Too Good"},
           "Actor": {"S": "Bindu"},
           "PostedBy": {"S": "User A"},
           "Songs": {"L": [ {"S": "Song1"},
                            {"S": "Song2"}
                          ]
                    }
            }'  

3. Query the table with Key Condition Expression with just the Partition Key

• You should see 3 items in the result set, as the primary key Title references 3 items in Movies table

• Refer the Movies table to check

• --item '{
  "Title": {"S": "Abhimaan"}
  

aws dynamodb query \
    --table-name Movies \
    --key-condition-expression 'Title = :Title' \
    --expression-attribute-values '{
        ":Title" : {"S": "Abhimaan"}
    }' \
    --return-consumed-capacity TOTAL

4. Query the table with Key Condition Expression with Partition Key and Sort key, using "=" option

• You should see only 1 item in the result set, as the primary key Title and sort key Review references 1 item in Movies table

• Refer the Movies table to check

• --item '{
  "Title": {"S": "Abhimaan"},
  "Review": {"S": "Too Good"}
  

aws dynamodb query \
    --table-name Movies \
    --key-condition-expression 'Title = :Title and Review = :rv' \
    --expression-attribute-values '{
        ":Title" : {"S": "Abhimaan"},
        ":rv" : {"S": "Too Good"}
    }' \
    --return-consumed-capacity TOTAL

5. Query the table with Filter Expression to limit our results based on non-key attributes

• In the table Movies , Title from non-key attribute Actor Bindu:

• --item '{
  "Title": {"S": "Abhimaan"},
  "Actor": {"S": "Bindu:}
  

aws dynamodb query \
    --table-name Movies \
    --key-condition-expression 'Title = :Title' \
    --filter-expression 'Actor = :person' \
    --expression-attribute-values '{
        ":Title" : {"S": "Abhimaan"},
        ":person" : {"S": "Bindu"}
    }' \
    --return-consumed-capacity TOTAL

6. Query the table to limit the number of items with Max items and Scan index forward

• It gets the 1st item from the table

• --item '{
  "Title": {"S": "Abhimaan"},
  "Actor": {"S": "Big B:}
  

aws dynamodb query \
    --table-name Movies \
    --key-condition-expression 'Title = :Title' \
    --expression-attribute-values '{
        ":Title" : {"S": "Abhimaan"}
    }' \
    --max-items 3 \
    --scan-index-forward  \
    --return-consumed-capacity TOTAL

7. Query the table to limit the number of items with Max items and No Scan index forward

• It gets the last item from the table

• --item '{
  "Title": {"S": "Abhimaan"},
  "Actor": {"S": "Bindu:}
  

aws dynamodb query \
    --table-name Movies \
    --key-condition-expression 'Title = :Title' \
    --expression-attribute-values '{
        ":Title" : {"S": "Abhimaan"}
    }' \
    --max-items 1 \
    --no-scan-index-forward  \
    --return-consumed-capacity TOTAL

8. Query the table to count the items in the result set

** Showing ScannedCount** When the Key Condition Expression matches 3 items, then it is 3.

• --item '{ "Title": {"S": "Abhimaan"}
• Count 3
• ScannedCount 3

aws dynamodb query \
    --table-name Movies \
    --key-condition-expression 'Title = :Title' \
    --expression-attribute-values '{
        ":Title" : {"S": "Abhimaan"}
    }' \
    --return-consumed-capacity TOTAL

** Showing Count** When the Filter Expression reduces the result set to 1 item, then it is 1.

• --item '{ "Title": {"S": "Abhimaan"}, "Actor": {"S": "Bindu:}
• Count 1
• ScannedCount 3

aws dynamodb query \
    --table-name Movies \
    --key-condition-expression 'Title = :Title' \
    --filter-expression 'Actor = :person' \
    --expression-attribute-values '{
        ":Title" : {"S": "Abhimaan"},
        ":person" : {"S": "Bindu"}
    }' \
    --return-consumed-capacity TOTAL

Cleanup

• Delete the table Movies

What we have done so far

We have successfully demonstrated Data Pane operations on DynamoDB item collections using Query. We used the criteria - Key condition expression and Filter Expression, with options, such as max items, scan-index-forward, no-scan-index-forward, Count and ScannedCount.