0

I have a DynamoDb table with the following structure

key       reference          value
1234      3214#0012345       xxxx
1234      3314#0003455       xxxx
1234      3214#0004231       xxxx

The value of reference, is a concatenation like so:

${accountId}#${idOfItem}

I'm trying to query the table to retrieve value but only where the account ID's match my query. So far I tried this

public Promise<List<Value>> getFiltersByUser(Key key, AccountId accountId) {
        final QuerySpec query = new QuerySpec();
        Map<String, Object> valueMap = new HashMap<>();
        Map<String, String> keyMap = new HashMap<>();

        keyMap.put("#key", PARTITION_KEY_NAME);
        keyMap.put("#reference", RANGE_KEY_NAME);
        valueMap.put(":key", key);
        valueMap.put(":startReference", createFilterRef("0000000", accountId));
        valueMap.put(":endReference", createFilterRef("9999999", accountId));
//createFilterRef simple creates the reference by concatening the two values with hashtag
        query.withKeyConditionExpression("#key = :key AND #filterRef BETWEEN :startFilterRef AND :endReference")
                .withNameMap(keyMap)
                .withValueMap(valueMap);

        return Blocking.get(() -> {
            final ItemCollection<QueryOutcome> out = table.query(query);
            return newArrayList(out)
                    .stream()
                    .map(this::createValueFromItem)//we can disregard this as it converts the results received to a different structure
                    .collect(Collectors.toList());
        });
    }

The problem I'm having currently is that my condition expression seems to not care about my BETWEEN operator, or I'm doing something wrong, and retrieves all the values instead.

Any advice on how to reach my goal?

Jorge Guerreiro
  • 682
  • 6
  • 22
  • Why wouldn't it include everything? You asked for everything from 0000000#x to 9999999#x and all your values are going to fit between these two. As an aside (not your problem here, but the problem you'll hit next) you need to be very careful doing string sort on numbers, because "100" is between "1" and "2". – hunterhacker Aug 10 '22 at 17:16
  • Yes, I understand but I'm asking for everything that has idOfItem#0000000 to idOfItem#9999999 which means that in my example if I use 3214 as idOfItem I should only get 2 values no? – Jorge Guerreiro Aug 11 '22 at 07:43
  • So createFilterRef reverses the values? That’s not what your comment says. – hunterhacker Aug 11 '22 at 15:54

0 Answers0