CODEWITHSUNDEEP
pythonamazon-web-servicesamazon-dynamodbboto3
João Koritar
João Koritar

Reputation: 89

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

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:

  1. 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)
)

  1. 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)
)

  1. 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.

Upvotes: 1

Views: 9547

Answers (3)

Mark P
Mark P

Reputation: 468

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}')

Upvotes: 2

Seth Geoghegan
Seth Geoghegan

Reputation: 5747

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.

Upvotes: 2

hoangdv
hoangdv

Reputation: 16147

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)
)

Upvotes: 0

Related Questions