Python - How to query DynamoDB on GSI using primary and sort key

Question

Well, as the title suggest I want to query on my DynamoDB table using GSI with the primary key and sort key (both from the GSI). I tried some ways to do it, but any success.

I have a table with the url-date-index, the url is the primary key from from the GSI, and the date is the sort key.

I tried the following:

Using KeyConditionExpression with & comparator:

This one retrieved me the error: TypeError: expected string or bytes-like

boto3.resource('dynamodb').Table('table').query(
    IndexName='url-date-index',
    KeyConditionExpression=conditions.Key('url')).eq(url) & conditions.Key('date')).eq(date)
)

Using KeyConditionExpression and FilterExpression:

This retrieved the following error: Filter Expression can only contain non-primary key attributes

boto3.resource('dynamodb').Table('table').query(
    IndexName='url-date-index',
    KeyConditionExpression=conditions.Key('url')).eq(url),
    FilterExpression=conditions.Key('date')).eq(date)
)

Using ExpressionAttributeNames, ExpressionAttributeValues and KeyConditionExpression:

This returned anything, even not the item that matches the url and date on the table.

boto3.resource('dynamodb').Table('table').query(
    IndexName='url-date-index',
    ExpressionAttributeNames={
        '#n0': 'url',
        '#n1': 'date'
    },
    ExpressionAttributeValues={
        ':v0': url,
        ':v1': date
    },
    KeyConditionExpression='(#n0 = :v0) AND (#n1 = :v1)'
)

Does anyone know what I'm doing wrong or what I can do to make this work.

Seth Geoghegan · Accepted Answer · 2021-03-13 16:29:06Z

2

In your particular use-case, you'll want to use ExpressionAttributeNames since your attribute names url and date are reserved words in DynamoDB.

The DynamoDB docs on querying secondary idnexes gives an example of a properly structured query, which we can apply to your situation:

Using this as a guide, we can construct what the arguments to your query operation should look like. For example

 {
    "TableName": "table",
    "IndexName": "url-date-index",
    "KeyConditionExpression": "#pk = :pk And #sk = :sk",
    "ExpressionAttributeNames": {"#pk":"url","#sk":"date"},
    "ExpressionAttributeValues": {":pk": {"S":url},":sk": {"S":date}}}
}

If this still doesn't work for you, consider checking out the NoSQL Workbench For DynamoDB. Among it's many useful features, it has an Operation Builder that helps you construct DynamoDB operations using a graphical interface. You can even run the operation against your live database. Once you have the operation working as you want, the tool can then translate the operation into a complete Phython, Javascript(Node) or Java code sample, which you can use to see how the operation is constructed.

answered Mar 13, 2021 at 16:29

Seth Geoghegan

5,7672 gold badges11 silver badges25 bronze badges

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

3 Comments

João Koritar Over a year ago

It worked! I just don't understand why since it's basically the same thing as the 3rd example I showed.

Seth Geoghegan Over a year ago

Great! Your example did not include types in the ExpressionAttributeValues. Notice how I'm defining url and date as strings

João Koritar Over a year ago

Aah that makes sense! Thank you by helping me!

Mark P · Accepted Answer · 2024-04-28 22:45:12Z

Just you need to create KeyConditionExpression with two expression. One with partition key and other with sort key.

This video helps step by step to find the answers and all DynamoDB related queries ( insert, delete, query using GSI, query using Local index etc) https://youtu.be/x8IxY4zoBGI

The python code is like below. The customer_id is the partition key and order_id is the sort key.

def query_by_partition_key_and_sort_key(customer_value, order_value):
    response = {}
    filtering_exp = Key('customer_id').eq(customer_value)
    filtering_exp2 = Key('order_id').eq(order_value)
    response = demo_table.query(
        KeyConditionExpression=filtering_exp and filtering_exp2)
    
    for item in response["Items"]:
        print(f'Item: {item}')

hoangdv · Accepted Answer · 2021-03-13 02:10:13Z

0

query is a function with some named arguments (IndexName, KeyConditionExpression, ...).

Let's try to call the function with named arguments as a normal function:

boto3.resource('dynamodb').Table('table').query(
    IndexName='url-date-index', 
    KeyConditionExpression=conditions.Key('url')).eq(url) & conditions.Key('date')).eq(date)
)

answered Mar 13, 2021 at 2:10

hoangdv

16.3k4 gold badges31 silver badges54 bronze badges

1 Comment

João Koritar Over a year ago

Owch, I saw that I've put the wrong syntax on the question, working with more than one language program can cause that lol. Actualy, I've used the correct syntax in my code. I'll fix it now. Btw, thanks

Collectives™ on Stack Overflow

Python - How to query DynamoDB on GSI using primary and sort key

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related