Querying a Table
Another common access pattern is reading multiple items from a table, based on your query criteria.
SQL
The SQL 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.
DynamoDB
PartiQL
DynamoDB provides the ExecuteStatement
action for retrieving multiple items using the select statement.
select AlbumTitle, Year, Price FROM Music WHERE Artist='No One You Know'
Note that this statement will return all items for the specified Artist.
For code examples using Select
and ExecuteStatement
,
see PartiQL Select Statements for DynamoDB.
Classic APIs
The Amazon DynamoDB Query
action lets you retrieve data in a similar
fashion. The Query
action provides quick, efficient access to the
physical locations where the data is stored. For more information, see Partitions and Data Distribution.
You can use Query
with any table that has a composite
primary key (partition key and sort key). You must specify an equality condition
for the partition key, and you can optionally provide another condition for the
sort key.
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" } }
For code examples that use Query
, see Getting Started with DynamoDB and AWS SDKs.