CODEWITHSUNDEEP
javascriptnode.jsamazon-web-servicesamazon-dynamodbdynamodb-queries
user13468285
user13468285

Reputation:

how to query dynamodb GSI with multiple operators

Lets say i have an entity with this model:

{
id: 'apples',
createdAt: 'some date'
rate: 430,
side: 'SELL',
status: 'OPEN',
GSI1: 'SELL#OPEN#430'
GSI2: 'apples'
}

i want to query using the GSI attributes of GSI2 beign the hash and GSI1 being the range. The query im looking for is get all apples(GSI2) where GSI1 begins with SELL#OPEN and >= SELL#OPEN#430 so basically im trying to get all apples being sold for 430 or greater and are open. Please how do i go about this using dynamodb query?

what i have done is:

params = {
      TableName: process.env.ORDERS_TABLE_NAME,
      IndexName: "GSI2_GSI1",
      KeyConditionExpression: `GSI2 = :item and ((begins_with(GSI2, :sideStatus) and >= :baseRate)`,
      ExpressionAttributeValues: {
        ":item": `apple`,
        ":baseRate": `SELL#OPEN#${rate}`,
        ":sideStatus": "SELL#OPEN",
      },
    };

thanks

Upvotes: 0

Views: 499

Answers (1)

cementblocks
cementblocks

Reputation: 4596

You can only operate on the Key attributes in the key condition expression. These parameters should do what you want because you have all the information in the GSI1 attribute

params = {
  TableName: process.env.ORDERS_TABLE_NAME,
  IndexName: "GSI2_GSI1",
  KeyConditionExpression: 'GSI2 = :item and GSI1 BETWEEN :lower AND :upper',
  ExpressionAttributeValues: {
    ":item": `apple`,
    ":lower": `SELL#OPEN#430`,
    ":upper": "SELL#OPEN#999", // you can probably also use "SELL#OPEN$" because $ is the character following # in ascii order
  },
};

Note this assumes that your rate in the GSI1 attribute is left padded with 0s. You need the string to be sorted in the same order as the numbers so if the rate is 10 then you need to store SELL#OPEN#010. (Note you might need more leading 0s depending on the maximum rate.)

Upvotes: 1

Related Questions