CODEWITHSUNDEEP
javaamazon-dynamodb
Dan
Dan

Reputation: 1033

Conditional DynamoDb Queries

I have a DynamoDb table called Users. I am trying to execute a very simple query where the user's last name is like 'John*' (Johnson, Johnston, Johnny, etc), however I cannot find a very straight forward example.

Below is a snippet of my code:

public class DynamoDbUsersTest extends ApplicationTest {
      @Autowired
      private DynamoDb dynamoDb;

      private Table usersTable = dynamoDb.getTable("Users");

      public void getUsersByLastNameContainsTest(){
          //userTable.contains(user.getLastName());  // No such method.
          userTable.scan(new ScanFilter("lastName").contains("John");
          ...
      }
}

Can someone please point me in the right direction? I tried looking at the Query Object, but I'm not sure it'll do what I need it to.

I begrudgingly had to use the Scan Object to make this work, however it does a full table scan with each query. Has anyone found a better/faster way of searching with partial values? Would the following be faster? 

public class DynamoDbUsersTest extends ApplicationTest {
      @Autowired
      private DynamoDb dynamoDb;

      private Table usersTable = dynamoDb.getTable("Users");

      public void getUsersByLastNameContainsTest(){
          userTable.query(new QueryFilter("lastName").contains("John");
          ...
      }
}

Upvotes: 0

Views: 2302

Answers (3)

notionquest
notionquest

Reputation: 39226

Here is the ScanSpec using contains to match the string partially.

Table table = dynamoDB.getTable("tablename");

        Map<String, Object> attributeValueMap = new HashMap<>();
        attributeValueMap.put(":lastNameValue", "John");

        ScanSpec scanSpec = new ScanSpec().withFilterExpression("contains (lastName,:lastNameValue)")
                .withValueMap(attributeValueMap);
        IteratorSupport<Item, ScanOutcome> scanOutcome = null;

        scanOutcome = table.scan(scanSpec).iterator();

while (scanOutcome.hasNext()) {
            System.out.println("Output ==============>" + scanOutcome.next().toJSON());
        }

Edit:-

As mentioned in other answers, you can use QuerySpec if you know the hash key and lastname is not defined as hash key. Also, you can use the contains on FilterExpression only. In other words, it can't be used on KeyConditionExpression.

QuerySpec needs KeyConditionExpression which will only support equality operator on hash key attribute. And then on FilterExpression, you can use contains on non-key attributes.

Upvotes: 0

Logan Pickup
Logan Pickup

Reputation: 2374

You cannot use contains as part of a query, only a scan; but if you only care about results starting with a particular value, then you can use the begins_with query operator: begins_with (a, substr) (where a is your key name). For this to work, the key must be a sort key, not a partition key. See http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/QueryAndScan.html#QueryAndScan.Query and http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/QueryingJavaDocumentAPI.html

Upvotes: 0

Alexander Patrikalakis
Alexander Patrikalakis

Reputation: 5195

Substring matching is something done well by ElasticSearch. I suggest you turn on your DynamoDB table's stream and index your items/documents in an AWS ElasticSearch cluster.

Upvotes: 1

Related Questions