DynamoDB and its Data Pane Operations - 2
Tags: dynamodb, cli, aws, crud
Published: 2022-12-20 22:14:18

This is the 2nd article in the series for **[Data Pane 1 (read-write) operations](https://dev.to/aws-builders/dynamodb-and-its-data-pane-operations-1-2772)**. 

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](https://dev.to/aws-builders/dynamodb-and-its-control-pane-operations-1-36bk)** and **[DynamoDB and its Control Pane Operations - 2](https://dev.to/aws-builders/dynamodb-and-its-control-pane-operations-2-327b)**.

## 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](https://github.com/awsmine/DynamoDB_projects)** 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](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Introduction.html)

[Query cli](https://docs.aws.amazon.com/cli/latest/reference/dynamodb/query.html)


# 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
```



![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d3lnr9b92kds3hs105hw.png)


## 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"}
                          ]
                    }
            }'  
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/np7g96tnaqy67ocg2d6o.png)


## 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
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/it4yafjfw7j3a6p4efa9.png)



## 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
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/upaew624qafbswra5a43.png)


## 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
```


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f4sblhdcwg503pbct60f.png)

## 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`**.