DynamoDB Query FilterExpression Multiple Condition Chaining Python

Question

I am trying to programmatically create a FilterExpression in Python for a DynamoDB query based on user provided parameter(s) for a specific Attribute (let's call it 'ATTRIBUTE1').

All user provided parameters which I need to filter for are in a list. For example: ['Parameter1', 'Parameter2']

Which would then take the form Attr('ATTRIBUTE1').eq(PARAMETER1)&Attr.('ATTRIBUTE1').eq(PARAMETER2)

How can I programmatically create an Attr for my FilterExpression like the above which is based on a changing number of user provided parameter(s)?

Sometimes I might have ['Parameter1'] and another time I might have ['Parameter1', 'Parameter2', 'Parameter3'] which need to turn into Attr('ATTRIBUTE1').eq('Parameter1') and Attr('ATTRIBUTE1').eq('Parameter1')&Attr('ATTRIBUTE1').eq('Parameter2')&Attr('ATTRIBUTE1').eq('Parameter3'), respectively.

I haven't been able to find a solution for this yet and would appreciate any guidance. Thanks in advance.

Jack Casey · Accepted Answer · 2020-05-14 02:09:07Z

11

Here's a compact way I was able to get this working:

from functools import reduce
from boto3.dynamodb.conditions import Key, And

response = table.scan(FilterExpression=reduce(And, ([Key(k).eq(v) for k, v in filters.items()])))

For example, filters would be a dict like:

{
    'Status': 'Approved', 
    'SubmittedBy': 'JackCasey'
}

answered May 14, 2020 at 2:09

Jack Casey

1,87615 silver badges20 bronze badges

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

2 Comments

Stan Over a year ago

Exactly what I want when I don't know how many conditions there are

Davi Wesley Over a year ago

@jack can you explain this reduce approach or give other examples that results in the same approach?

Jedi3112 · Accepted Answer · 2019-12-04 11:46:06Z

4

You can try the following. I run through the loop of query params to build the dynamodb conditions.

    if event.get('queryStringParameters'):
        query_params = event.get('queryStringParameters')
        for query_param, value in query_params.items():
            if query_param == 'attribute1':
                filter_expression_list.append(Attr('attribute1').gte(value))
            if query_param == 'attribute2':
                filter_expression_list.append(Attr('attribute2').eq(value))
            if query_param == 'attribute3':
                filter_expression_list.append(Attr('attribute3').eq(value))
    FilterExpression = get_filter_expression(filter_expression_list)

Update:

Can use the following code to get the filter expression. This will handle the case when there are 2 or more than 2 expressions

def get_filter_expression(filter_expression_list):
    filter_expression = None
    first = True
    for filter in filter_expression_list:
        if first:
            filter_expression = filter
            first = False
        else:
            filter_expression = filter_expression & filter
    return filter_expression

edited Dec 4, 2019 at 11:46

answered Mar 25, 2019 at 2:25

Jedi3112

3171 gold badge3 silver badges11 bronze badges

3 Comments

Simon Christian Over a year ago

Thanks for this answer, it set me on the right track with a similar problem, however I found that the And() class will only combine two conditions, and actually needed to add an And() for each condition being added.

faberfedor Over a year ago

Where are you guys getting this And() function? When I try it I get NameError: name 'And' is not defined

Jedi3112 Over a year ago

@faberfedor updated the answer. Can use the logical &

ozbey · Accepted Answer · 2017-10-07 14:46:43Z

2

Combination of FilterExpression in a string form and ExpressionAttributeValues can work, consider following example:

attrs = ["attribute1", "attribute2", "attribute3"]
user_input = ["parameter1", "paramater2", "parameter3"]
ExpressionAttributeValues = {}
FilterExpression = "";
for index, input in enumerate(attrs):
    if(len(attrs)-1 == index): FilterExpression += input+"=:"+input
    else: FilterExpression += input+" = :"+input + " AND ";

for index, input in enumerate(user_input):
    AttrName = ":" + attrs[index]
    ExpressionAttributeValues[AttrName] = {
        "S" : input
    }

print(ExpressionAttributeValues) 
print(FilterExpression)

then you can use these two in your query, more on here http://boto3.readthedocs.io/en/latest/reference/services/dynamodb.html#client

answered Oct 7, 2017 at 14:46

ozbey

1601 silver badge7 bronze badges

3 Comments

listentoreason Over a year ago

Thanks this is great. As a follow-up question, I'd like to filter the query if an attribute (not the partition or sort key) is greater than x and less than y. I'm getting the following error when attempting this and have tried a bunch of different alternatives with no luck: Invalid FilterExpression: Incorrect operand type for operator or function; operator or function: >=, operand type: M: My FilterExpression and ExpressionAttributeValues as follow: FilterExpression = 'ATTRIBUTE >= :x AND ATTRIBUTE <= :y' ExpressionAttributeValues = {':x': {'N': '100'}, ':y': {'N': '1000'}}. Thanks!

ozbey Over a year ago

You are trying to use these operators on a map attribute, that's why you are getting this error. This should work ExpressionAttributeValues = {':x': 100, ':y': 1000}

listentoreason Over a year ago

Thanks much for the help - I've got it up and running, and have learned quite a bit about DynamoDB querying. Thanks again.

America · Accepted Answer · 2019-04-18 19:52:17Z

2

A compact, Pythonic approach might be to manage the criteria in a dictionary, avoid the looping in favor of comprehensions, and use the string format method:

criteria = {'my_key1': 'my_value1', 'my_key2': 'my_value2'}
FilterExpression = ' AND '.join(['{0}=:{0}'.format(k) for k, v in criteria.items()])
ExpressionAttributeValues = {':{}'.format(k): {'S': v} for k, v in criteria.items()}

answered Apr 18, 2019 at 19:52

America

4182 gold badges5 silver badges16 bronze badges

Comments

Ansuman · Accepted Answer · 2021-01-05 12:48:33Z

My solution for chaining multiple filter expression in python.

from boto3.dynamodb.conditions import Key, Attr  
 ...
 users_table_dynamo.query(
    KeyConditionExpression=Key('pk').eq('SC#' + sc_id) & Key('sk').begins_with("GA#"),
    FilterExpression=eval("Attr('attribute1').eq('value1') & Attr('attribute2').eq('value2') & Attr('attribute3').eq('value3')"))

Also i can write something like this, for dynamic expression creation:

def constructFilterExpression(tag_ids):
expression = ""
for idx, item in enumerate(tag_ids):
    if idx == 0:
        expression += f"Attr('user_tag_id').eq('{str(item)}')"
    else:
        expression += f" & Attr('user_tag_id').eq('{str(item)}')"

return expression

so finally my query is:

filterExpression = constructFilterExpression(tag_ids)
item = users_table_dynamo.query(
    KeyConditionExpression=Key('pk').eq('SC#' + scenario_id) & Key('sk').begins_with("AT#"),
    FilterExpression=eval(filterExpression),
    ProjectionExpression="article_id")

Mohamed Atif Nassar · Accepted Answer · 2020-10-14 08:16:35Z

1

i = 0
for key, val in attr_value_dic.items():
    if i == 0:
        filterCondition = Attr(key).eq(val)
    filterConidtion = filterCondition & Attr(key).eq(val) 
    i = i + 1

response = table.query(
    KeyConditionExpression=Key(partition_key).eq(search_value)
    ,FilterExpression=filterCondition

    )

answered Oct 14, 2020 at 8:16

Mohamed Atif Nassar

313 bronze badges

1 Comment

ZF007 Over a year ago

4 posted answers and one accepted as best. So add context why your solution is the best. End of Review.

Collectives™ on Stack Overflow

DynamoDB Query FilterExpression Multiple Condition Chaining Python

6 Answers 6

2 Comments

3 Comments

3 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

3 Comments

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related