CODEWITHSUNDEEP
amazon-web-servicesamazon-dynamodb
Praveenkumar
Praveenkumar

Reputation: 981

How can I Use begins_with method on primary key in DynamoDB?

I have Table with 2 attribute id(string,primary key), value(string). When I try follwoing KeyConditionExpression it throws Query key condition not supported.

KeyConditionExpression: "begins_with(ID, :tagIDValue)" 
                        or
KeyConditionExpression: "contains(ID, :tagIDValue)"

From this link I came to know we can use only EQ operations on main key. How can I achieve this

Solution:======================================================

I need to use begins_with or contains to filter So I went with following approach.

Table attributes: PK(partion_key, string), ID(sort key, string), value(string).

Now my primary key is framed based on PK,ID

PK will have constant value for all rows. so KeyConditionExpression will be like.

KeyConditionExpression: "PL = :pk  and begins_with(ID, :tagIDValue)"

NOTE: But still contains not working with KeyConditionExpression. I think it was removed from KeyConditionExpression

Upvotes: 37

Views: 82363

Answers (5)

Karthik Kumar
Karthik Kumar

Reputation: 11

The below query will work on DynamoDB PartiSQL:

SELECT * FROM TABLENAME WHERE begins_with("COLUMNNAME", 'BEGINNING LETTER OR WORD')

  1. Replace with your table TABLENAME
  2. Replace your COLUMNNAME
  3. Replace your starting letter BEGINNING LETTER OR WORD

Upvotes: 1

kam
kam

Reputation: 109

You cannot use the begins_with condition expression on the Hash(primary) key. It is only applicable with the scan method and Range(sort) key.

The other thing you can do is create a GlobalSecondaryIndexes with ID as the Hash key.

For Example:

  IntentTable:
     Type: AWS::DynamoDB::Table
     Properties:
       BillingMode: PAY_PER_REQUEST
       KeySchema:
         - AttributeName: id
           KeyType: HASH
         - AttributeName: timestamp
           KeyType: RANGE
       AttributeDefinitions:
         - AttributeName: client
           AttributeType: S
         - AttributeName: timestamp
       GlobalSecondaryIndexes:
         - IndexName: byID
           KeySchema:
             - AttributeName: ID
               KeyType: HASH
             - AttributeName: timestamp
               KeyType: RANGE
           Projection:
             ProjectionType: ALL

And then now you can use the query using that index instead of scan. Avoid scan at all costs, or you will be shocked with the monthly billing.

Upvotes: 8

Mitchell Tyson
Mitchell Tyson

Reputation: 151

I would stay away from scans at all costs, in this scenario I would create a global secondary index. The primary key on a GSI doesn't require uniqueness and you can query off of that. Storage is cheap, you can probably live with the extra keys.

Upvotes: 15

lukaserat
lukaserat

Reputation: 4896

I disagree with Tolbahady statement regarding begins_with and contains only workable in range key. You can use any comparison operator in any keys using method scan. Scan is a flexible but expensive and inefficient way of conducting dynamodb queries.

Anyways, there is a tool named, AWS NoSQL Workbench. It is little bit like MySQL Workbench. What is the good thing about it, is you can construct your table while checking against your access patterns(possible and most used queries of your application against your table).

Upvotes: 7

Tolbahady
Tolbahady

Reputation: 591

You can use begins_with and contains only with a range key after specifying an EQ condition for the primary key.

To use EQ with the primary key you can do 

KeyConditionExpression: "ID = :tagIDValue"

Upvotes: 42

Related Questions