DynamoDB sorting data with sort key not working

Question

I'm using dynamoDb to store some additional info but I have some troubles sorting my data.

I have the following create syntax where I have a storeId and number keys. I set the number as sort key, but the problem is that the data isn't sorted at all.

$response = $dynamoDb->createTable([
    'TableName' => 'foo',
    'KeySchema' => [
        [
            'AttributeName' => 'storeId',
            'KeyType' => 'HASH'  //Partition key
        ],
        [
            'AttributeName' => 'number',
            'KeyType' => 'RANGE' // sort Key
        ]
    ],
    'AttributeDefinitions' => [
        [
            'AttributeName' => 'storeId',
            'AttributeType' => 'N'
        ],
        [
            'AttributeName' => 'number',
            'AttributeType' => 'N'
        ]
    ],
    'ProvisionedThroughput' => [
        'ReadCapacityUnits' => 20,
        'WriteCapacityUnits' => 20
    ]
]);

My scan params:

$scanParams = [
    'TableName' => 'foo',
    'ProjectionExpression' => '#storeId, #number',
    'FilterExpression' => '#number >= :n',
    'ExpressionAttributeNames'=> [ '#storeId' => 'storeId', '#number' => 'number'],
    'ExpressionAttributeValues' => [
        ':n' => ['N' => $number]
    ]
];

The result of my scan:

StoreId number
68001   80000
25000   37000
463501  527000
4800001 5300000
360001  400000
2000001 2600000

As you can see, the data isn't sorted on the number property.

You can only get sorted results on a query, not a scan. And for the query you must provide the storeId value. Essentially you can only get sorted results of a partition, not the entire table. — idbehold
– idbehold, Commented Apr 24, 2017 at 20:40
@idbehold I only know the value of the sort key, so not the storeId and both keys needs to be specified in the query — user3601546
– user3601546, Commented Apr 25, 2017 at 6:38

notionquest · Accepted Answer · 2017-04-24 20:40:21Z

Just read the first paragraph below. The sort key is used to store all the items of the same partition key value physically close together and sorted in ascending order by default (i.e. important point is data is not sorted across the partition key). In other words, the data is sorted in ascending order by default for the same partition key.

Example:-

Partition Key Sort Key

p1, s1

p1, s2

p1, s3

p2, s1

p2, s2

If the table has a composite primary key (partition key and sort key), DynamoDB calculates the hash value of the partition key in the same way as described in Data Distribution: Partition Key—but it stores all of the items with the same partition key value physically close together, ordered by sort key value.

To write an item to the table, DynamoDB calculates the hash value of the partition key to determine which partition should contain the item. In that partition, there could be several items with the same partition key value, so DynamoDB stores the item among the others with the same partition key, in ascending order by sort key.

To read an item from the table, you must specify its partition key value and sort key value. DynamoDB calculates the partition key's hash value, yielding the partition in which the item can be found.

In Query API, there is a parameter to get the result in ascending or descending order.

ScanIndexForward: true || false

ScanIndexForward — (Boolean)

Specifies the order for index traversal: If true (default), the traversal is performed in ascending order; if false, the traversal is performed in descending order.

Items with the same partition key value are stored in sorted order by sort key. If the sort key data type is Number, the results are stored in numeric order. For type String, the results are stored in order of ASCII character code values. For type Binary, DynamoDB treats each byte of the binary data as unsigned.

If ScanIndexForward is true, DynamoDB returns the results in the order in which they are stored (by sort key value). This is the default behavior. If ScanIndexForward is false, DynamoDB reads the results in reverse order by sort key value, and then returns the results to the client.

Query API

And how can i read an item when I only know the sort key value, and not the partition key?
Two options. 1) Use scan api to scan the whole table with filter expression (note scan is a costly operation). 2) Create GSI with sort key of main table as partition key of the index.

Collectives™ on Stack Overflow

DynamoDB sorting data with sort key not working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related