Conditional DynamoDb Queries

Question

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");
          ...
      }
}

Alexander Patrikalakis · Accepted Answer · 2017-03-08 03:00:44Z

1

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.

answered Mar 8, 2017 at 3:00

Alexander Patrikalakis

5,2251 gold badge35 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Logan Pickup · Accepted Answer · 2017-03-08 03:45:05Z

0

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

answered Mar 8, 2017 at 3:45

Logan Pickup

2,3632 gold badges23 silver badges29 bronze badges

3 Comments

Dan Over a year ago

Thanks. I will check today and make sure the key is a sorted key. So, given that I have two keys (contract_number and vendor_name), is it possible to only a single key, say vendor_name, without having to use the other?

Logan Pickup Over a year ago

I don't believe so - a range key is optional, but a partition key is mandatory. In this case, I think you'd need to specify contract_number=x as well as begins_with(vendor_name, john). The partition key doesn't have to be unique, but the combination of partition key and range key must be. I''m pretty sure that with these restrictions, DynamoDB is not a good choice of database for your use case - it really only handles the case where you know what you want to find, it does not handle traditional DB queries so well. You will probably be better off using RDS or similar.

Dan Over a year ago

I was able to get this up and running using contractNumber as my partition key and vendorName as my secondary key. I then run a scan for what I'm looking for. It's able to handle the 70K records in 2MS :)

notionquest · Accepted Answer · 2017-03-08 06:38:32Z

0

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.

edited Mar 8, 2017 at 6:38

answered Mar 7, 2017 at 22:55

notionquest

39.4k6 gold badges120 silver badges111 bronze badges

4 Comments

Dan Over a year ago

thank you. How is this any different than what I have above?

Dan Over a year ago

Instead of using a ScanSpec, I wonder if you could use a QuerySpec instead to avoid the full table scans

Dan Over a year ago

QuerySpec querySpec = new QuerySpec(new QueryFilter("lastName").contains("john")); table.query(querySpec);

notionquest Over a year ago

Updated the answer. Hope it clarifies.

Collectives™ on Stack Overflow

Conditional DynamoDb Queries

3 Answers 3

Comments

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related