How to properly use Partition and Sort Key

Question

I know how to use the PK and SK but in my case it's a bit different.

I basically pass some distance and I want to retrieve the vehicles that can be used.

e.g:

HTTP Request passing the distance e.g. 50km so (bycicle, car and train need to be returned)

Basically I want to retrieve the vehicles that can be used

PK | distanceMin (SK) | distanceMax | vehicleName

distance | 0 | 10 | bycicle

distance | 10 | 500 | car // cannot have SK as 0

distance | 50 | 9999 | train // cannot have SK as 0

// This will have thousands of records

The problem is that I need to do scans for this and it's cost expensive and I also cannot use the same SK (0) for car and train because it would be a duplicate. How can I pass a distance and make a query to get all the vehicles that are inside the range distanceMin and distanceMax?

My current code

searchByDistance: async function(distance) {
  const search = parseInt(distance);
  let params = {
    TableName: this.myTable,
    ScanIndexForward: true,
    ConsistentRead: false,
    KeyConditionExpression: 'PK=:pkKey AND distanceMin <= :search',
    ProjectionExpression: 'vehicleName',
    ExpressionAttributeValues: {
      ':pkKey': 'distance',
      ':search': search,
    },
    FilterExpression: 'distanceMax >= :search',
  };

  let scanResults = [];
  let items;
  try {
    do {
      items = await this.getDbClient().query(params).promise();
      (items.Items || []).forEach((item) => scanResults.push(item));
      params.ExclusiveStartKey = items.LastEvaluatedKey;
    } while (typeof items.LastEvaluatedKey !== 'undefined');
  } catch (err) {
    console.log(err, err.stack);
  }

  return scanResults;
},

How to properly use Partition and Sort Key

0 Answers