Query
Relational databases allow you to run a SELECT SQL statement with a where clause that can use any table column in the where clause, on the other hand In DDB Query - you can only use the key attributes, that can only use key attributes
The Query operation in Amazon DynamoDB finds items based on primary key values. The primary key value is specified by way of a Key Condition Expression.
Key Condition Expression
Partition Key in the expression can only use equality operator. Effectively what that means is that query operation is run on a single partition!!! For Sort Key there are a bunch of operations that you can use for creating the selection criteria for selecting the items in the collection.
The Sort Key part of the expression can use operators and functions.
Example
In the illustration, the Partition Key for the table is the customer’s email-id and the Sort Key is OrderDate#Number. The query with the shown Key condition expression will return the orders placed after 2020/09/01.
Query processing
The router uses the Partition Key to route the request to the correct partion. The query processing logic that applies to the partion consists of 3 steps:
- Apply Sort Key selection criteria
- Apply the Filter Expression
- Apply the Projections
Review the illustration below, it extends the earlier example by adding the Filter and ProjectionExpression.
Query operation
The Query operation returns zero or more items from a table. Items returned from the call have the same Partition Key. The returned items are selected based on the Sort Key selection criteria in the Key Condition Expression.
-
TableName is a required parameter
-
IndexName is for scanning a table
-
KeyConditionExpression is the item selection criteria
-
Limit is the maximum number of items to evaluate. If DynamoDB processes the number of items up to the limit while processing the results, it stops the operation and returns the matching values up to that point, and a key in LastEvaluatedKey to apply in a subsequent operation.
-
ProjectionExpression comma separated string with one or more attribute names to be retrieved
-
FilterExpression specifies the item selection criteria
-
ScanIndexForward specifies the order for index traversal: If true (default), the traversal is performed in ascending order; if false, the traversal is performed in descending order.
-
Select specifies the attributes to be returned in the result.
-
ALL_ATTRIBUTES If you query a local secondary index, then for each matching item in the index, DynamoDB fetches the entire item from the parent table.
-
ALL_PROJECTED_ATTRIBUTES applies to the index; returns the attributes projected in the index
-
COUNT returns the number of matching items
-
SPECIFIC_ATTRIBUTES is equivalent to ProjectionExpression
-
aws dynamodb query \
--table-name Employee \
--key-condition-expression 'LoginAlias = :alias' \
--expression-attribute-values '{
":alias": {"S": "johns"}
}'