Filter Expressions
Building thoughtful indexes can make queries simple and performant, though sometimes you need to apply further filters to your query.
FilterExpressions
Below demonstrates how to add a FilterExpression
to Dynamo’s DocumentClient directly alongside how you would accomplish the same using the where
method.
Example
animals.query
.exhibit({ habitat: "Africa" })
.where(
({ isPregnant, offspring }, { exists, eq }) => `
${eq(isPregnant, true)} OR ${exists(offspring)}
`,
)
.go();
Equivalent Parameters
{
"KeyConditionExpression": "#pk = :pk and begins_with(#sk1, :sk1)",
"TableName": "zoo_manifest",
"ExpressionAttributeNames": {
"#isPregnant": "isPregnant",
"#offspring": "offspring",
"#pk": "gsi1pk",
"#sk1": "gsi1sk"
},
"ExpressionAttributeValues": {
":isPregnant0": true,
":pk": "$zoo#habitat_africa",
":sk1": "$animals_1#enclosure_"
},
"IndexName": "gsi1pk-gsi1sk-index",
"FilterExpression": "#isPregnant = :isPregnant0 OR attribute_exists(#offspring)"
}
Where Clause
The where()
method allow you to write a FilterExpression
or ConditionExpression
without having to worry about the complexities of expression attributes. To accomplish this, ElectroDB injects an object attributes
as the first parameter to all Filter Functions, and an object operations
, as the second parameter. Pass the properties from the attributes
object to the methods found on the operations
object, along with inline values to set filters and conditions.
Provided
where
callbacks must return a string. All method on theoperation
object all return strings, so you can return the results of theoperation
method or use template strings compose an expression.
Examples
A single filter expression
animals.query
.exhibit({ habitat: "Africa", enclosure: "5b" })
.where((attr, op) => op.eq(attr.dangerous, true))
.go();
A single filter expression with destructuring
animals.query
.exhibit({ habitat: "Africa", enclosure: "5b" })
.where(({ dangerous }, { eq }) => eq(dangerous, true))
.go();
Multiple filter expressions
animals.query
.exhibit({ habitat: "Africa", enclosure: "5b" })
.where(
(attr, op) => `
${op.eq(attr.dangerous, true)} AND ${op.notExists(attr.lastFed)}
`,
)
.go();
Chained usage (implicit AND)
animals.query
.habitats({ habitat: "Africa", enclosure: "5b" })
.where((attr, op) => `
${op.eq(attr.dangerous, true)} OR ${op.notExists(attr.lastFed)}
`)
.where(({ birthday }, { between }) => {
const today = Date.now();
const lastMonth = today - 1000 * 60 * 60 * 24 * 30;
return between(birthday, lastMonth, today);
})
.go();
“dynamic” filtering
type GetAnimalOptions = {
habitat: string;
keepers: string[];
};
function getAnimals(options: GetAnimalOptions) {
const { habitat, keepers } = options;
return animals.query.exhibit({ habitat })
.where(({ keeper }, { eq }) => {
return keepers.map((name) => eq(keeper, name)).join(' AND ');
}).go()
}
const { data, cursor } = await getAnimals({
habitat: "RainForest",
keepers: ["Joe Exotic", "Carol Baskin"],
});
Operations
The attributes
object contains every Attribute defined in the Entity’s Model. The operations
object contains the following methods:
operator | example | result |
---|---|---|
eq | eq(rent, maxRent) | #rent = :rent1 |
ne | ne(rent, maxRent) | #rent <> :rent1 |
gte | gte(rent, value) | #rent >= :rent1 |
gt | gt(rent, maxRent) | #rent > :rent1 |
lte | lte(rent, maxRent) | #rent <= :rent1 |
lt | lt(rent, maxRent) | #rent < :rent1 |
begins | begins(rent, maxRent) | begins_with(#rent, :rent1) |
exists | exists(rent) | attribute_exists(#rent) |
notExists | notExists(rent) | attribute_not_exists(#rent) |
contains | contains(rent, maxRent) | contains(#rent = :rent1) |
notContains | notContains(rent, maxRent) | not contains(#rent = :rent1) |
between | between(rent, minRent, maxRent) | (#rent between :rent1 and :rent2) |
size | size(rent) | size(#rent) |
type | type(rent, 'N') | attribute_type(#rent, 'N') |
name | name(rent) | #rent |
value | value(rent, maxRent) | :rent1 |
escape | escape(123) , escape('abc') | #123 , #abc |
field | field('field_name') | :field_name |
ElectroDB Functions
The filter functions available above all come from the list of functions supported by DynamoDB directly. ElectroDB offers a few functions that offer some additional convenience when creating Filter Expressions.
Note: If ElectroDB ever lags behind in implementing FilterExpression functions, you can use
value()
,name()
,escape()
, and/orfield()
to simply template out the implementation!
Name
The name()
function allows you to create a reference to an attribute’s name, which can be useful to create filters referencing an attribute as it is currently stored. The function will add a ExpressionAttributeNames
property for this record and return the partial expression for use in your filter.
This example demonstrates how you might find animals that were last fed by someone other than their “keeper”.
animals.query
.exhibit({ habitat: "Africa", enclosure: "5b" })
.where(
({ lastFedBy, keeper }, { name }) => `
${name(lastFedBy)} != ${name(keeper)}
`,
)
.go();
Value
The value()
function can be paired with name()
as convenience when building unique filters. The value()
method is passed an attribute (used to enforce type), and a value for that attribute. ElectroDB will add a ExpressionAttributeValues
property for this value, and return a reference to that value that can be used in your FilterExpression
.ExpressionAttributeValues
This example shows how you can implement the eq
operation without using the ElectroDB eq
function
animals.query
.exhibit({ habitat: "Africa", enclosure: "5b" })
.where(
({ keeper }, { name, value, eq }) => `
${name(keeper)} = ${value(keeper, "Tiger King")}
`,
)
.go();
Escape
The escape()
method allows you to provide any primitive to an expression without the need for a specific attribute. In many ways, this function is like a less restrictive version of value()
. This can be useful when using static values and/or when creating custom FilterExpressions
This example shows how you might use escape to apply a filter against the size()
of an attribute.
animals.query
.exhibit({ habitat: "Africa", enclosure: "5b" })
.where(
({ diet }, { size, escape }) => `
${size(diet)} > ${escape(2)}
`,
)
.go();
Field
The field()
method allows you to provide and reference a field value that is not present in your model. This method is similar to escape()
but is used for field names.
You can use both escape()
and field()
to template any filter that DynamoDB supports.
animals.query
.exhibit({ habitat: "Africa", enclosure: "5b" })
.where(
(_, { field, escape }) => `
contains(${field("gsi1sk")}, ${escape("value")})
`,
)
.go();
Advanced Usage
Where with Complex Attributes
ElectroDB supports using the where()
method with DynamoDB’s complex attribute types: map
, list
, and set
. When using the injected attributes
object, simply drill into the attribute itself to apply your update directly to the required object.
The following are examples on how to filter on complex attributes:
Filtering on a map
attribute
animals.query
.farm({ habitat: "Africa" })
.where(({ veterinarian }, { eq }) => eq(veterinarian.name, "Herb Peterson"))
.go();
Filtering on an element in a list
attribute
animals.query
.exhibit({ habitat: "Tundra" })
.where(({ offspring }, { eq }) => eq(offspring[0].name, "Blitzen"))
.go();
Multiple Where Clauses
It is possible to include chain multiple where clauses. The resulting FilterExpressions (or ConditionExpressions) are concatenated with an implicit AND
operator.
let MallStores = new Entity(model, { table: "StoreDirectory" });
let stores = await MallStores.query
.leases({ mallId: "EastPointe" })
.between({ leaseEndDate: "2020-04-01" }, { leaseEndDate: "2020-07-01" })
.where(
({ rent, discount }, { between, eq }) => `
${between(rent, "2000.00", "5000.00")} AND ${eq(discount, "1000.00")}
`,
)
.where(
({ category }, { eq }) => `
${eq(category, "food/coffee")}
`,
)
.go();
{
"TableName": "StoreDirectory",
"ExpressionAttributeNames": {
"#rent": "rent",
"#discount": "discount",
"#category": "category",
"#pk": "idx2pk",
"#sk1": "idx2sk"
},
"ExpressionAttributeValues": {
":rent1": "2000.00",
":rent2": "5000.00",
":discount1": "1000.00",
":category1": "food/coffee",
":pk": "$mallstoredirectory_1#mallid_eastpointe",
":sk1": "$mallstore#leaseenddate_2020-04-01#storeid_",
":sk2": "$mallstore#leaseenddate_2020-07-01#storeid_"
},
"KeyConditionExpression": "#pk = :pk and #sk1 BETWEEN :sk1 AND :sk2",
"IndexName": "idx2",
"FilterExpression": "(#rent between :rent1 and :rent2) AND (#discount = :discount1 AND #category = :category1)"
}