DynamoDB,how to query with BEGINS_WITH

Question

i'm using DocumentClient for query. and using serverless framework with DynamoDb.

i'm trying to query with BEGINS_WITH without providing any primary key.

here is how my data looks like:

[
  {
    id: 1,
    some_string: "77281829121"
  },
  {
    id: 2,
    some_string: "7712162hgvh"
  },
  {
    id: 3,
    some_string: "7212121"
  }
]

here is my serverless.yml [i.e Table config i guess]:

Resources:
 IPRecord:
      Type: 'AWS::DynamoDB::Table'
      Properties:
        TableName: ${file(./serverless.js):Tables.IPRecord.name}
        BillingMode: PAY_PER_REQUEST
        AttributeDefinitions:
          - AttributeName: 'id'
            AttributeType: 'S'
          - AttributeName: 'some_string'
            AttributeType: 'S'
        KeySchema:
          - AttributeName: 'id'
            KeyType: 'HASH'
        GlobalSecondaryIndexes:
          - IndexName: ${file(./serverless.js):Tables.IPRecord.index.ID}
            KeySchema:
              # ...some more index goes here
              - AttributeName: 'some_string'
                KeyType: 'RANGE'
            Projection:
              ProjectionType: 'ALL'

Q: Using DocumentClinet i want to query with the first few elements of some_string. which will return all the docs, that is matching. like in this case i want to query {some_string:"77"} and it will return

 [{
    id: 1,
    some_string: "77281829121"
  },
  {
    id: 2,
    some_string: "7712162hgvh"
  }]

currently my query looks like this [this gives error ][Running in Local DynamoDB JS shell]:

var params = {
    TableName: '<TABLE_NAME>',
    IndexName: '<INDEX_NAME>',
    KeyConditionExpression: 'begins_with(some_string,:value)',
    ExpressionAttributeValues: { 
      ':value': '77'
    }
};
docClient.query(params, function(err, data) {
    if (err) ppJson(err);
    else ppJson(data); 
});

seems like this above query needs a primary key, and in my case that is id. if i pass that, then it will point to a single doc.

Here is what i have achived so far:

var params = {
    TableName: '<TABLE_NAME>',
    FilterExpression: 'begins_with(some_string,:value)',
    ExpressionAttributeValues: { 
      ':value': '77'
    },
    Select:'COUNT' //as i only required COUNT
};
docClient.scan(params, function(err, data) {
    if (err) ppJson(err);
    else ppJson(data); 
});

this above query does what i want.but any better approach or solution always welcome.

what is the usecase that you are trying to solve, dynamodb does not support beginswith without primary key. — best wishes
@bestwishes so, i'm trying to get all the docs, in which some_string starts with say "77" , — Saikat Chakrabortty
that is the plan :) which wont work(at least with this approach), but what is some_string, how are they related, — best wishes
sorry, i'm not able to catch you right way. but yeah some_string is a string field, can contain a random string. like 56uijkhfrtu. if you look at the serverless.yml you can also get, i have added GSI for this. — Saikat Chakrabortty

best wishes best wishes · Accepted Answer · 2019-02-06T02:40:31

if number of characters in your beginswith query is always going to be random, i don't see an option solving it with dynamodb.

but let's say there are going to be at least 3 characters. then you can do the following.

Update your dynamodb schema to

 IPRecord:
      Type: 'AWS::DynamoDB::Table'
      Properties:
        TableName: ${file(./serverless.js):Tables.IPRecord.name}
        BillingMode: PAY_PER_REQUEST
        AttributeDefinitions:
          - AttributeName: 'id'
            AttributeType: 'S'
          - AttributeName: 'some_string'
            AttributeType: 'S'
        KeySchema:
          - AttributeName: 'id'
            KeyType: 'HASH'
          - AttributeName: 'some_string'
            KeyType: 'RANGE'

And instead of storing

[
  {
    id: 1,
    some_string: "77281829121"
  },
  {
    id: 2,
    some_string: "7712162hgvh"
  },
  {
    id: 3,
    some_string: "7212121"
  }
]

store as

[
  {
    id: 772,
    uniqueid:1,
    some_string: "77281829121"
  },
  {
    id: 771,
    uniqueid:2,
    some_string: "7712162hgvh"
  },
  {
    id: 721,
    uniqueid:3,
    some_string: "7212121"
  }
]

Where id is always the first 3 character of original some_string.

Now let's say you have to query all items that start with abcx you can do

select * where id=abc and some_string startswith abcx

but you should always try to have more number of characters in id so that load is randomly distributed. for example if there are only 2 character only 36*36 ids are possible if there are 3 character 36*36*36 ids are possible.

DynamoDB,how to query with BEGINS_WITH

1 Answers