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?

Answer 1

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?

Answer 2

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