Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions,
see Getting Started with Amazon Web Services in China
(PDF).
Differences in querying a table
Another common access pattern is reading multiple items from a table, based on
your query criteria.
Querying a table with
SQL
When using SQL the SELECT
statement lets you query on key
columns, non-key columns, or any combination. The WHERE
clause
determines which rows are returned, as shown in the following examples.
/* Return a single song, by primary key */
SELECT * FROM Music
WHERE Artist='No One You Know' AND SongTitle = 'Call Me Today';
/* Return all of the songs by an artist */
SELECT * FROM Music
WHERE Artist='No One You Know';
/* Return all of the songs by an artist, matching first part of title */
SELECT * FROM Music
WHERE Artist='No One You Know' AND SongTitle LIKE 'Call%';
/* Return all of the songs by an artist, with a particular word in the title...
...but only if the price is less than 1.00 */
SELECT * FROM Music
WHERE Artist='No One You Know' AND SongTitle LIKE '%Today%'
AND Price < 1.00;
Note that the primary key for this table consists of
Artist and SongTitle.
Querying a table in
DynamoDB
In Amazon DynamoDB, you can use either the DynamoDB API or PartiQL (a SQL-compatible query
language) to query an item from a table.
- DynamoDB API
-
With Amazon DynamoDB, you can use the Query
operation to
retrieve data in a similar fashion. The Query
operation
provides quick, efficient access to the physical locations where the
data is stored. For more information, see Partitions and data distribution in DynamoDB.
You can use Query
with any table or secondary index.
You must specify an equality condition for the partition key's
value, and you can optionally provide another condition for the sort
key attribute if it is defined.
The KeyConditionExpression
parameter specifies the
key values that you want to query. You can use an optional
FilterExpression
to remove certain items from the
results before they are returned to you.
In DynamoDB, you must use ExpressionAttributeValues
as
placeholders in expression parameters (such as
KeyConditionExpression
and
FilterExpression
). This is analogous to the use of
bind variables in relational databases,
where you substitute the actual values into the SELECT
statement at runtime.
Note that the primary key for this table consists of
Artist and
SongTitle.
The following are some DynamoDB Query
examples.
// Return a single song, by primary key
{
TableName: "Music",
KeyConditionExpression: "Artist = :a and SongTitle = :t",
ExpressionAttributeValues: {
":a": "No One You Know",
":t": "Call Me Today"
}
}
// Return all of the songs by an artist
{
TableName: "Music",
KeyConditionExpression: "Artist = :a",
ExpressionAttributeValues: {
":a": "No One You Know"
}
}
// Return all of the songs by an artist, matching first part of title
{
TableName: "Music",
KeyConditionExpression: "Artist = :a and begins_with(SongTitle, :t)",
ExpressionAttributeValues: {
":a": "No One You Know",
":t": "Call"
}
}
- PartiQL for DynamoDB
-
With PartiQL, you can perform a query by using the
ExecuteStatement
operation and the
Select
statement on the partition key.
SELECT AlbumTitle, Year, Price
FROM Music
WHERE Artist='No One You Know'
Using the SELECT
statement in this way returns all
the songs associated with this particular
Artist
.
For code examples using Select
and
ExecuteStatement
, see PartiQL select statements for DynamoDB.