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, Feb 05 '19 at 12:30
@bestwishes so, i'm trying to get all the docs, in which `some_string ` starts with say "77" , — Saikat Chakrabortty, Feb 05 '19 at 13:35
that is the plan :) which wont work(at least with this approach), but what is some_string, how are they related, — best wishes, Feb 05 '19 at 15:16
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, Feb 05 '19 at 15:46

best wishes · Answer 1 · 2019-02-06T05:51:04.657

0

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.

edited Feb 06 '19 at 05:51

answered Feb 06 '19 at 02:40

best wishes

5,789
1
34
59

`number of characters in your beginswith query is always going to be random,` this will always going to be random. there is no relation with the id field as well right now with. although right now i'm using `.scan` which returns the expectd data aswell. but not seem to be effecient. – Saikat Chakrabortty Feb 06 '19 at 04:40
@saikat yes, what i am saying is, let's say in your query you will always have 3 character, make those 3 chars as primary key. so that you can query instead of scan. – best wishes Feb 06 '19 at 05:14
got the point, now the case is, when i'm gooing to search with say 5chars then it will increase the complexity. also this 3, chars if i make them id, anytime it can make conflict. so `id` wont be unique anymore. – Saikat Chakrabortty Feb 06 '19 at 05:29
you can update your schema to have some_string as sort key. after that id won't not have to be unique, id+some_string has to be unique, you can get away from GSI1 since that is not solving any purpose anyways. – best wishes Feb 06 '19 at 05:45
`i'm going to search with say 5chars then it will increase the complexity.` it won't cause any issue , as long as you have minimum query as id. – best wishes Feb 06 '19 at 05:47
sorry, unfortunately it didnt, but i appreciate this idea. for my use case implementing this is kinda complex as its having some dependent things. looking for few more approaches though. – Saikat Chakrabortty Feb 06 '19 at 10:25

DynamoDB,how to query with BEGINS_WITH

1 Answers1

Linked