4

I cannot find any detailed documentation for dynamodb BETWEEN operator especially how it deals with strings.

For example if I make queries like "... sort_key Between '100' and '150' " and sort key is "120" then it works correct. But if I make strings like "1224|67" or "6653|67" and then query for between "3456|67" and "8968|67", it doesn't work. How to get information how this comparison works internally?

Kirimaks
  • 41
  • 1
  • 6

1 Answers1

2

In your case you need to use expression attribute names and expression attribute values. This is a very good practice in any DynamoDB query.

Please note that you'll also need to set a partition key (see #pk and :pk below).

aws dynamodb query \
    --table-name TABLE_NAME \
    --key-condition-expression "#pk = :pk AND #sk BETWEEN :a and :b" \
    --expression-attribute-names '{"#sk": "sort_key", "#pk": "partition_key"}' \
    --expression-attribute-values '{":a": {"S":"3456|67"}, ":b": {"S":"8968|67"}, ":pk":{"S":"partition_key_value"}}'

I didn't use this syntax for a while, there may me some small syntax error, but this is what you have to do in order to query with BETWEEN.

See the last example about KeyConditions:

aws dynamodb query \
    --table-name Music \
    --key-condition-expression 'Artist = :a AND SongTitle BETWEEN :t1 AND :t2' \
    --expression-attribute-values '{
        ":a": {"S": "No One You Know"}, 
        ":t1": {"S": "A"}, 
        ":t2": {"S": "M"}
    }'
Costin
  • 2,699
  • 5
  • 25
  • 43
  • Hi Thanks for the answer, this is exactly how I do but I'm using boto3. The point of my question is that sometimes I'm getting bizarre results when I make queries with compound strings. Seems it works for strings which are separated by "_" instead of "|". – Kirimaks Oct 09 '19 at 01:16
  • Interesting... I cannot test it now, but I'll give it a try. Do you have a deadline soon or can it wait for the weekend? And what do you mean by "**sometimes**"? Feel free to edit the question to explain that "sometimes" :) – Costin Oct 09 '19 at 08:18
  • Hi, I will be grateful for any information. About the "sometimes", I meant that it works unpredictable. For example If I query with hash key like: "{timestamp}_{id}" it doesn't work, but "{id}_{timestamp}" works. With "|" as separator sometimes works sometimes not (unperictable). – Kirimaks Oct 13 '19 at 17:04
  • 2
    @Kirimaks you may want to zero-pad the elements of your compound strings, e.g. `000123|000420`. That's because dynamo comparison is lexicographical, meaning that `1 < 10 < 2` and you probably want `1 < 2 < 10`, so make it `01 < 02 < 10`. – Dima Tisnek Jun 15 '21 at 06:12
  • You are right @DimaTisnek, it is often necessary to pad the content – Costin Jun 16 '21 at 13:55