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?