Dynamodb sort key not returning data in sorted order-CodePudding

I have created a sort key SK and I am retrieving messages using begins_with query on messsages

the SK is being added as "message_< UTC Date_TIME>_chatid"

here date 5 Jan is coming before 4 Jan but to retrieve data in sorted order it should be after 4 Jan What can I do to retrieve the messages in sorted order according to the date in reverse order ,

 params = {
  TableName: "test_chat",
  ScanIndexForward: false,

  KeyConditionExpression: "PK = :pkval and begins_with(SK, :skval)",
  ExpressionAttributeValues: {
    ":pkval": chatId,
    ":skval": "message_",
  },
  Limit: 10,
};
let result = await dynamoDbClient.query(params).promise();
console.log(result);

CodePudding user response：

DynamoDB collates and compares strings using the bytes of the underlying UTF-8 string encoding. For example, "a" (0x61) is greater than "A" (0x41), and "¿" (0xC2BF) is greater than "z" (0x7A).

You can use the string data type to represent a date or a timestamp. One way to do this is by using ISO 8601 strings, as shown in these examples:

2016-02-15
2015-12-21T17:42:34Z
20150311T122706Z

For more information, see wikipedia

You can also call this Lexicographical sorting. The term lexicographical often refers to the mathematical rules or sorting. These include, for example, proving logically that sorting is possible. Read more about lexicographical order on wikipedia

Alphabetical ordering includes variants that differ in how to handle spaces, uppercase characters, numerals, and punctuation. Purists believe that allowing characters other than a-z makes the sort not "alphabetic" and therefore it must fall in to the larger class of "lexicographic". Again, wikipedia has additional details.

In computer programming, a related question is dictionary order or ascii code order. In dictionary order, the uppercase "A" sorts adjacent to lowercase "a". However, in many computer languages, the default string compare will use ascii codes. With ascii, all uppercase letters come before any lowercase letters, which means that that "Z" will sort before "a". This is sometimes called ASCIIbetical order.

In your example it is only comparing Thu and Wed as they are the first unique characters of your String:

str1 = "message_Thu, 05 Jan 2023 11:27:16 GMT_3b60";
str2 = "message_Wed, 04 Jan 2023 10:43:16 GMT_3b60";

console.log(str1>str2);

false

CodePudding user response：

Your Sort Key is a String type. DynamoDB is sorting the strings as instructed: message_Wed > message_Thurs.

ISO 8601 format date strings like 2023-01-05T12:22:33.034Z are better suited to date sorting.