CODEWITHSUNDEEP
databaseamazon-web-servicesnosqlamazon-dynamodbdynamodb-queries
javakid1993
javakid1993

Reputation: 255

Query or Scan DynamoDB for all rows which have an attribute that matches a list of options

I have a DynamoDB table that has the following format

{
    id: "1234" (Primary Key, String)
    billNumber: "01" (Sort Key, String)
    month: 1 (Number)
    product: "Apple" (String)
    itemLocation": "Aisle 1" (String)
}

Each product is written to the table separately, so the products can't be written to the same row and they can't update the existing entry to append to the value in the product field.

I want to know how to query or scan this DDB table to find all itemLocations that id "1234" has purchased in the month 1 where the value within the Product field matches list of given products.

I also have a global secondary index on id-month which I can use to find all rows purchased in a month by a user.

Meaning if the table looked like 

id   | billNumber | month |  product    |  itemLocation
1234 |     01     |   1   |   Apple     |  Aisle 1
1234 |     02     |   1   |   Banana    |  Aisle 2
1234 |     03     |   1   |   Cherry    |  Aisle 3
1234 |     04     |   1   |   Coke      |  Aisle 4

and I wanted to get the itemLocations that id "1234" bought in month "1" where the products were one of {"Apple", "Banana", "Cherry"} I would be returned every row but the Coke row as seen below.

id   | billNumber | month |  product    |  itemLocation
1234 |     01     |   1   |   Apple     |  Aisle 1
1234 |     02     |   1   |   Banana    |  Aisle 2
1234 |     03     |   1   |   Cherry    |  Aisle 3

Is this possible in a single query or scan without needing to query for each Product separately? I believe I could solve the problem in my example by doing 3 queries. The same id and month in all 3 queries and a different Product in each query.

The closest I've gotten to seeing something that could work is DDB Condition Expression for this but that doesn't seem to be used for query, instead for CRUD operations.

Upvotes: 0

Views: 2073

Answers (2)

sigma1510
sigma1510

Reputation: 1291

Adding a newer answer here for anyone else who comes across this problem later:

You can transform your list of products into a String Set (type SS) and query your DDB using your id and contains(StringSet, product) as a filter expression.

Upvotes: 1

Charles
Charles

Reputation: 23823

You can't have a DDB table that looks like that...

With DDB, when using a composite primary key (hash + sort) the combination must be unique.

So you can't have

id   | month |  product   |  itemLocation
1234 |   1   |   Apple    |  Aisle 1
1234 |   1   |   Banana   |  Aisle 2
1234 |   1   |   Cherry   |  Aisle 3
1234 |   1   |   Coke     |  Aisle 4

in which 4 records have the same hash key (ID) and sort key(month)

EDIT
Ok so now you've got a valid DDB table...

But you can't query() it for

that id "1234" bought in month "1" where the products were one of {"Apple", "Banana", "Cherry"}

Scan() would work, but very inefficiently...

In order to do this efficiently, you'll want an local secondary index with a sort key of month#product

Now, depending on how many products, you'll need to query 3 times

Query(table, hk='1234', sk='1#Apple')
Query(table, hk='1234', sk='1#Banana')
Query(table, hk='1234', sk='1#Cherry')

Or only once and filter the products server side or client side (note that filtering server side doesn't save any Read Capacity Units) 

Query(table, hk='1234', sk BEGINS WITH '1#')

Upvotes: 2

Related Questions