What is the sense of Query in AWS DynamoDB

Question

In AWS DynamoDB you must specify a `partition key, which make it just work like GetItem ... because partition key is unique so it is supposed to return only one item, so if I know the ID of that item, it make no sense anymore to query! because query meant to be for constrains ...

so can someone give me example where querying one partition key can return multiple items ?

# Create single-attribute primary key table
aws dynamodb create-table --table-name testdb6 --attribute-definitions '[{"AttributeName": "Id", "AttributeType": "S"}]' --key-schema '[{"AttributeName": "Id", "KeyType": "HASH"}]' --provisioned-throughput '{"ReadCapacityUnits": 5, "WriteCapacityUnits": 5}' 

# Populate table
aws dynamodb put-item --table-name testdb6 --item '{ "Id": {"S": "1"}, "LastName": {"S": "Lopez"}, "FirstName": {"S": "Maria"}}'
aws dynamodb put-item --table-name testdb6 --item '{ "Id": {"S": "2"}, "LastName": {"S": "Fernandez"}, "FirstName": {"S": "Augusto"}}'

# Query table using only partition attribute
aws dynamodb query --table-name testdb6 --select ALL_ATTRIBUTES --key-conditions '{"Id": {"AttributeValueList": [{"S": "1"}], "ComparisonOperator": "EQ"}}'

You also can only use the EQ Operator for partition key, so using for example BETWEEN or OR or IN is not allowed on partition key

alternative to query there is scan but

scan is expensive (slow)
you can't sort on scan

Update

so I realized I can use sort key, and then in this case partition key can be my table name, so I need to change my vocabularies

Table -> Database
Partition Key -> Table / Collection
Sort Key -> Primary Key / ObjectId

Example : table my-api with partition key -> className and sort key -> id

my-api
   className | id | username | title
   _User     |  0 | "bingo"  |
   _User     |  1 | "mimi"   |
   _Song     |  0 |          | "You with me"

it is weird design

query is used to filter by columns other that partition key, for example by sort key — Iłya Bursov
– Iłya Bursov, Commented May 31, 2017 at 18:52

notionquest · Accepted Answer · 2017-05-31 18:56:22Z

3

If the table has both partition key and sort key and query by partition key alone will give you multiple items.

Partition key - must for using Query API
Sort key - optional while using Query API

Get API:-

For a composite primary key, you must provide values for both the partition key and the sort key.

So, Get API will always return only one item. Also, there is no filter expression to filter by non-key attributes.

answered May 31, 2017 at 18:56

notionquest

39.4k6 gold badges120 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Srini Sydney Over a year ago

Filter by non -key attribute - You can create 'global secondary index' using the non - key attribute as the partition key. There is a limit of 5 gsi per table.

Parth Modi · Accepted Answer · 2017-06-01 17:03:44Z

0

Querying is for finding Items based on Primary or Index Key attributes. Primary Key can have one Partition Key (hash key) and one optional Range Key. If Range key present, then you can have multiple records with same Partition Key. So to perform GetItem operation on this kind of composite primary key you need to specify Range Key with Hash key.

Also, You can specify multiple Global Secondary Indexes (GSI) and Local Secondary Indexes (LSI), through which you can query non-key attributes.

So, Query operation provides you means to find items based on either Primary key, LSI or GSI's hash key and Range key's attributes.

Now, the example you used, is certainly not a good approach to design your schema.

Table != Database

Table == Table and DynamoDB houses all the tables.

User, Song etc needs to be stored in different tables. Then you can specify id, username to further uniquely identify items of your table. Each item can be thought of as record in RDBMS. Read more about choosing primary key and indexes and all information from AWS DynamoDB Developer Guide

answered Jun 1, 2017 at 17:03

Parth Modi

1,8734 gold badges22 silver badges27 bronze badges

2 Comments

user8065363 Over a year ago

tell me how to query a user with its email without knowing its id or username supposing you have a Table User with id as partition key and username as sort/range key, and every id will be associated with 1 user only?

Parth Modi Over a year ago

Yes, every id will be associated with a user. Or, if your each user will always have unique username then you can specify username as partition key. But that's your preference, and for querying through email, you can specify email as Hash key of Global Secondary Index, which will allow you to query users via email.

mgalala · Accepted Answer · 2017-06-02 11:33:40Z

0

Partition key would always return unique result if there is no sort key defined. Configuring sort key to your table means that the combination between partition key and sort key must be unique. For best practise defining the structure of your table please refer to this

answered Jun 2, 2017 at 11:33

mgalala

1068 bronze badges

Collectives™ on Stack Overflow

What is the sense of Query in AWS DynamoDB

Update

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related