how to query dynamodb GSI with multiple operators

Question

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

What are the keys for your GSIs? Your key condition expression can only reference the key attributes (partition and sort key). — cementblocks
– cementblocks, Commented May 13, 2021 at 12:27

cementblocks · Accepted Answer · 2021-05-13 13:09:10Z

1

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.)

answered May 13, 2021 at 13:09

cementblocks

4,64520 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

how to query dynamodb GSI with multiple operators

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related