How to query AWS DynamoDB using multiple Indexes?

Question

I have an AWS DynamoDb cart table with the following item structure -

{
  "cart_id": "5e4d0f9f-f08c-45ae-986a-f1b5ac7b7c13",
  "user_id": 1234,
  "type": "OTHER",
  "currency": "INR",
  "created_date": 132432423,
  "expiry": 132432425,
  "total_amount": 90000,
  "total_quantity": 2,  
  "items": [
    {
      "amount": 90000,
      "category": "Laptops",
      "name": "Apple MacBook Pro",
      "quantity": 1
    }
  ]
}

-

{
  "cart_id": "12340f9f-f08c-45ae-986a-f1b5ac7b1234",
  "user_id": 1234,
  "type": "SPECIAL",
  "currency": "INR",
  "created_date": 132432423,
  "expiry": 132432425,
  "total_amount": 1000,
  "total_quantity": 2,  
  "items": [
    {
      "amount": 1000,
      "category": "Special",
      "name": "Special Item",
      "quantity": 1
    }
  ]
}

The table will have cart_id as Primary key,
user_id as an Index or GSI,
type as an Index or GSI.

I want to be able to query the cart table,
to find the items which have user_id = 1234 AND type != "SPECIAL".
I don't know if this means for the query -

--key-condition-expression "user_id = 1234 AND type != 'SPECIAL'"

I understand that an AWS DynamoDb table cannot be queried using multiple indexes at the same time,
I came across the following question, it has a similar use case and the answer is recommending creating a composite key,
Querying with multiple local Secondary Index Dynamodb

Does it mean that while putting a new item in the table,
I will need to maintain another column like user_id_type,
with its value as 1234SPECIAL and create an Index / GSI for user_id_type ?

Sample item structure -

{
  "cart_id": "5e4d0f9f-f08c-45ae-986a-f1b5ac7b7c13",
  "user_id": 1234,
  "type": "OTHER",
  "user_id_type" : "1234OTHER",
  "currency": "INR",
  "created_date": 132432423,
  "expiry": 132432425,
  "total_amount": 90000,
  "total_quantity": 2,  
  "items": [
    {
      "amount": 90000,
      "category": "Laptops",
      "name": "Apple MacBook Pro",
      "quantity": 1
    }
  ]
}

References -
1. Querying with multiple local Secondary Index Dynamodb
2. Is there a way to query multiple hash keys in DynamoDB?

qkhanhpro · Accepted Answer · 2019-05-09 04:06:36Z

3

Your assumption is correct. Maybe you can add into that a delimitter field1_field2 or hash them if either of them is too big in size hashOfField1_hashOfField2

That mean spending some more processing power on your side, however. As DynamoDB does not natively support It.

Composite key in DynamoDB with more than 2 columns?

Dynamodb: query using more than two attributes

Additional info on your use case

KeyConditionExpression only allowed for the hash key. You can put it in the FilterExpression

Why is there no **not equal** comparison in DynamoDB queries?

edited May 9, 2019 at 4:06

answered May 9, 2019 at 3:40

qkhanhpro

5,3504 gold badges43 silver badges66 bronze badges

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

2 Comments

Dev1ce Over a year ago

How are others able to query the table using KeyConditionExpression? Will I be able to achieve my use case without a new column and passing the following in the KeyConditionExpression = "user_id = :user_id and type <> 'SPECIAL'", do the attributes in the KeyConditionExpression all need to be Indexes?

qkhanhpro Over a year ago

A added some more information on your use case. Hope this helps

Sabito · Accepted Answer · 2021-01-31 16:15:05Z

Does it mean that while putting a new item in the table, I will need to maintain another column like user_id_type, with its value as 1234SPECIAL and create an Index / GSI for user_id_type?

The answer is it depends on how many columns (dynamodb is schema-less, by a column I mean data field) you need and are you happy with 2 round trips to DB.

your query:

     user_id = 1234 AND type != "SPECIAL"

1- if you need all information in the cart but you are happy with two round trips:

Solution: Create a GSI with user_id (HASH) and type (RANGE), then add cart_id (base table Hash key) as projection.
Explanation: so, you need one query on index table to get the cart_id given user_id and type --key-condition-expression "user_id = 1234 AND type != 'SPECIAL'" then you need to use cart_id(s) from the result and make another query to the base table

2- if you do not need all of cart information.

Solution: you need to create a GSI and make user_id HASH and type as RANGE and add more columns (columns you need) to projections.
Explanation: projection is additional columns you want to have in your index table. So, add some extra columns, which are more likely to be used as a result of the query, to avoid an extra round trip to the base table
Note: adding too many extra columns can double your costs, as any update on base table results in updates in GSI tables projection fields)

3- if you want just one round trip and you need all data

then you need to manage it by yourself and your suggestion can be applied

MattS · Accepted Answer · 2022-02-18 22:21:15Z

0

One possible answer is to create a single index with a sort key. Then you can do this:

{
  TableName: "...",
  IndexName: "UserIdAndTypeIndex",
  KeyConditionExpression: "user_id = :user_id AND type != :type",
  ExpressionAttributeValues: {
    ":user_id": 1234,
    ":type": "SPECIAL"
  }
}

answered Feb 18, 2022 at 22:21

MattS

1801 silver badge9 bronze badges

Comments

Hrvoje · Accepted Answer · 2022-03-25 11:33:04Z

-1

You can build GraphQL schema with AWS AppSync from your DynamoDB table and than query it in your app with GraphQL. Link

answered Mar 25, 2022 at 11:33

Hrvoje

15.4k11 gold badges103 silver badges121 bronze badges

Collectives™ on Stack Overflow

How to query AWS DynamoDB using multiple Indexes?

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related