dynamodb how to query by sort key only?

Question

I have written some python code, I want to query dynamoDB data by sort key. I remember I can use follow-up code successful:

 table.query(KeyConditionExpression=Key('event_status').eq(event_status))

My table structure column

primary key:event_id
sort key: event_status

I believe this can be done by creating a Global Secondary Index in the form of an Inverted Index. — Jarrett GXZ
– Jarrett GXZ, Commented Nov 5, 2021 at 6:43

Mathix420 · Accepted Answer · 2019-12-29 23:38:28Z

40

You have to create a global secondary index (GSI) for the sort key in order to query on it alone.

edited Dec 29, 2019 at 23:38

Mathix420

1,34719 silver badges29 bronze badges

answered Aug 17, 2018 at 20:46

mrbillyocean

1,4211 gold badge15 silver badges24 bronze badges

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

1 Comment

user2019716 May 21 at 8:18

would it be a good solution to set the event_status as primary key to avoid creating the gsi ?

Ben Dubuisson · Accepted Answer · 2022-12-16 21:16:21Z

13

If you don't want to scan (and maybe you shouldn't), you will need to create a GSI (Global Secondary Index) for that, and set event_status as the GSIPK.

so your table config will be:

 table = dynamodb.create_table(
        TableName="your_table",
        KeySchema=[
            {"AttributeName": "event_id", "KeyType": "HASH"},  # Partition key
            {"AttributeName": "event_status", "KeyType": "RANGE"},  # Sort key
        ],
        AttributeDefinitions=[
            {"AttributeName": "event_id, "AttributeType": "S"},
            {"AttributeName": "event_status", "AttributeType": "S"},
            {"AttributeName": "gsi_event_status", "AttributeType": "S"},
            {"AttributeName": "gsi_event_id", "AttributeType": "S"},
        ],
        GlobalSecondaryIndexes=[
            {
                "IndexName": "gsiIndex",
                "KeySchema": [
                    {"AttributeName": "gsi_event_status", "KeyType": "HASH"},
                    {"AttributeName": "gsi_event_id", "KeyType": "RANGE"},
                ],
                "Projection": {"ProjectionType": "ALL"},
            },
        ],
        BillingMode="PAY_PER_REQUEST",
    )

Be mindful that GSIs can be expensive and you might wanna change the ProjectionType if you don't need all attributes.

Now you can query by pk:

table.query(KeyConditionExpression=Key('event_id').eq(event_id))

or by the GSI PK which is set to your sk:

lookup.query(
        IndexName="gsiIndex",
        KeyConditionExpression=Key("gsi_event_status").eq(event_status),
    )

edited Dec 16, 2022 at 21:16

answered Nov 26, 2021 at 3:36

Ben Dubuisson

75713 silver badges38 bronze badges

2 Comments

Mat-KH Over a year ago

The event_id and event_status are listed twice in the AttributeDefinitions. Is there a reason for that or doesn't it work the same with the duplicate entries removed?

Ben Dubuisson Over a year ago

I think it was meant to define attributes for main table and GSI, will update with different names so it's more obvious

notionquest · Accepted Answer · 2017-02-21 16:09:22Z

9

The scan API should be used if you would like to get data from DynamoDB without using Hash Key attribute value.

Example:-

fe = Attr('event_status').eq("new");

response = table.scan(
        FilterExpression=fe        
    )

for i in response['Items']:

print(json.dumps(i, cls=DecimalEncoder))

while 'LastEvaluatedKey' in response:
    response = table.scan(        
        FilterExpression=fe,        
        ExclusiveStartKey=response['LastEvaluatedKey']
        )

    for i in response['Items']:
        print(json.dumps(i, cls=DecimalEncoder))

answered Feb 21, 2017 at 16:09

notionquest

39.4k6 gold badges120 silver badges111 bronze badges

1 Comment

garnaat Over a year ago

Note that scanning is not the same as querying. You will be iterating over all the values so it is much less efficient than a query. If you really need to efficiently query on event_status you should consider creating a GSI for that field.

Hamid Jolany · Accepted Answer · 2021-11-22 16:01:38Z

1

According to the main concept of the sort key, it is part of main cluster in the partition key to define some filter expression with partition key in query. so there is no ability to search on the sort key alone and without partition key. unless to define a global secondary index on the sort key.

answered Nov 22, 2021 at 16:01

Hamid Jolany

8909 silver badges13 bronze badges

Comments

Prabhakar Reddy · Accepted Answer · 2020-07-15 06:47:33Z

0

By using FilterExpression we can scan the table using Sort key

NOTE: here LastUpdated is sortkey

Example:

from_date = "fromdate"
to_date = "todate"

dynamodb = boto3.resource('dynamodb', region_name='ap-south-1')
table = dynamodb.Table("your-tablename")
response =table.scan(
    FilterExpression=Attr('LastUpdated').between(from_date,to_date))
    )
result = response['Items']

edited Jul 15, 2020 at 6:47

answered Jul 15, 2020 at 6:34

Prabhakar Reddy

132 bronze badges

1 Comment

Ben Stickley Over a year ago

Be careful, this only filters up to 1MB of data that is returned from the original query. From the docs: "A filter expression is applied after a Query finishes, but before the results are returned"

Collectives™ on Stack Overflow

dynamodb how to query by sort key only?

5 Answers 5

1 Comment

2 Comments

1 Comment

Comments

By using FilterExpression we can scan the table using Sort key

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

2 Comments

1 Comment

Comments

By using FilterExpression we can scan the table using Sort key

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related