Dynamodb scan() using FilterExpression

Question

First post here on Stack and fairly new to programming with Python and using DynamoDB, but I'm simply trying to run a scan on my table that returns results based on two pre-defined attributes.

---Here is my Python code snippet---

shift = "3rd"
date = "2017-06-21"

if shift != "":
    response = table.scan(
        FilterExpression=Attr("Date").eq(date) and Attr("Shift").eq(shift)
    )

My DynamoDB has 4 fields.

1. ID
2. Date
3. Shift
4. Safety

Now for the issue, upon running I'm getting two table entries returned when I should only be getting the first entry... the one with "No safety issues" based on my scan criteria.

---Here is my DynamoDB return results---

[
  {
    "Shift": "3rd",  
    "Safety": "No safety issues",  
    "Date": "2017-06-21",
    "ID": "2"
  }, 
  {
    "Shift": "3rd", 
    "Safety": "Cut Finger", 
    "Date": "2017-06-22", 
    "ID": "4"
  }
]

Items Returned: 2

I believe that by applying the FilterExpression with the logical 'and' specified that the scan operation is looking for entries that meet BOTH criteria since I used 'and'.

Could this be because the 'shift' attribute "3rd" is found in both entries? How do I ensure it returns entries based on BOTH criteria being meet and not just giving me results from one attribute type?

I have a feeling this is simple but I've looked at the available documentation at: http://boto3.readthedocs.io/en/latest/reference/services/dynamodb.html#DynamoDB.Table.scan and am still having trouble. Any help would be greatly appreciated!

P.S. I tried to keep the post simple and easy to understand (not including all my program code) however, if additional information is needed I can provide it!

Solution 1

This is because you used Python's and keyword in your expression, instead of the & operator.

If a and b are both considered True, a and b returns the latter, b:

>>> 2 and 3
3

If any of them is False, or if both of them are, the first False object is returned:

>>> 0 and 3
0
>>> 0 and ''
0
>>> 

The general rule is, and returns the first object that allows it to decide the truthiness of the whole expression.

Python objects are always considered True in boolean context. So, your expression:

Attr("Date").eq(date) and Attr("Shift").eq(shift)

will evaluate as the last True object, that is:

Attr("Shift").eq(shift)

which explains why you only filtered on the shift.

You need to use the & operator. It usually means "bitwise and" between integers in Python, it is redefined for Attr objects to mean what you want: "both conditions".

So you must use the "bitwise and":

FilterExpression=Attr("Date").eq(date) & Attr("Shift").eq(shift)

According to the documentation,

You are also able to chain conditions together using the logical operators: & (and), | (or), and ~ (not).

Solution 2

Dynamodb scan() using FilterExpression

For multiple filters, you can use this approach:

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

filters = dict()
filters['Date'] = "2017-06-21"
filters['Shift'] = "3rd"

response = table.scan(FilterExpression=And(*[(Key(key).eq(value)) for key, value in filters.items()]))

Solution 3

Using parts from each of the above answers, 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()])))

Allows filtering upon multiple conditions in filters as a dict. For example:

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

Solution 4

Expanding on Maxime Paille's answer, this covers the case when only one filter is present vs many.

from boto3.dynamodb.conditions import And, Attr
from functools import reduce
from operator import and_

filters = dict()
filters['Date'] = "2017-06-21"
filters['Shift'] = "3rd"

table.scan("my-table", **build_query_params(filters))

def build_query_params(filters):
    query_params = {}
    if len(filters) > 0:
        query_params["FilterExpression"] = add_expressions(filters)

    return query_params

def add_expressions(self, filters: dict):
    if filters:
        conditions = []
        for key, value in filters.items():
            if isinstance(value, str):
                conditions.append(Attr(key).eq(value))
            if isinstance(value, list):
                conditions.append(Attr(key).is_in([v for v in value]))
        return reduce(and_, conditions)

View more solutions

Comments

[Torbyn]

Thank you for the explanation! I see now!

[ImGeorge]

Thanks a lot. You are a legend.

[ERR]

What if you want to filter for any Date before "2017-06-21", how would the code look like?

[Maxime Paillé]

Unfortunately, in this specific case this function only supports equality. A quick way would be: query = [(Key(key).eq(value)) for key, value in filter.items()][0] query = query & Attr("Date").lt('2017-06-21') result = table.scan(FilterExpression=query)

[Ashton Honnecke]

There's a variable mismatch, expressions.items() should be filters.items()

[bruce szalwinski]

@AshtonHonnecke Thanks! Fixed.

[Stan]

This reduce is used amazing