Conditions Expressions
Building thoughtful indexes can make queries simple and performant. Sometimes you need to filter results down further or add conditions to an update/patch/upsert/put/create/delete/remove action.
ConditionExpressions
Below is the traditional way you would add a ConditionExpression
to Dynamo’s DocumentClient directly alongside how you would accomplish the same using the where
method.
Example
animals.update({
animal: "blackbear",
name: "Isabelle"
})
// no longer pregnant because Ernesto was born!
.set({
isPregnant: false,
lastEvaluation: "2021-09-12",
lastEvaluationBy: "stephanie.adler"
})
// welcome to the world Ernesto!
.append({
offspring: [{
name: "Ernesto",
birthday: "2021-09-12",
note: "healthy birth, mild pollen allergy"
}]
})
// using the where clause can guard against making
// updates against stale data
.where(({isPregnant, lastEvaluation}, {lt, eq}) => `
${eq(isPregnant, true)} AND ${lt(lastEvaluation, "2021-09-12")}
`)
.go()
Equivalent Parameters
{
"UpdateExpression": "SET #isPregnant = :isPregnant_u0, #lastEvaluation = :lastEvaluation_u0, #lastEvaluationBy = :lastEvaluationBy_u0, #offspring = list_append(#offspring, :offspring_u0)",
"ExpressionAttributeNames": {
"#isPregnant": "isPregnant",
"#lastEvaluation": "lastEvaluation",
"#lastEvaluationBy": "lastEvaluationBy",
"#offspring": "offspring"
},
"ExpressionAttributeValues": {
":isPregnant0": true,
":lastEvaluation0": "2021-09-12",
":isPregnant_u0": false,
":lastEvaluation_u0": "2021-09-12",
":lastEvaluationBy_u0": "stephanie.adler",
":offspring_u0": [
{
"name": "Ernesto",
"birthday": "2021-09-12",
"note": "healthy birth, mild pollen allergy"
}
]
},
"TableName": "zoo_manifest",
"Key": {
"pk": "$zoo#animal_blackbear",
"sk": "$animals_1#name_isabelle"
},
"ConditionExpression": "#isPregnant = :isPregnant0 AND #lastEvaluation < :lastEvaluation0"
}
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 operation
animals.update({habitat: "Africa", enclosure: "5b"})
.set({keeper: "Joe Exotic"})
.where((attr, op) => op.eq(attr.dangerous, true))
.go();
A single filter operation with destructuring
animals.update({animal: "tiger", name: "janet"})
.set({keeper: "Joe Exotic"})
.where(({dangerous}, {eq}) => eq(dangerous, true))
.go();
Multiple conditions
animals.update({animal: "tiger", name: "janet"})
.set({keeper: "Joe Exotic"})
.where((attr, op) => `
${op.eq(attr.dangerous, true)} AND ${op.notExists(attr.lastFed)}
`)
.go();
Chained usage (implicit AND)
animals.update({animal: "tiger", name: "janet"})
.set({keeper: "Joe Exotic"})
.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;
const query = animals.query.exhibit({habitat});
for (const name of keepers) {
query.where(({keeper}, {eq}) => eq(keeper, name));
}
return query.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 | eq(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) |
name | name(rent) | #rent |
value | value(rent, maxRent) | :rent1 |
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.update({animal: "tiger", name: "janet"})
.set({keeper: "Joe Exotic"})
.where(({veterinarian}, {eq}) => eq(veterinarian.name, "Herb Peterson"))
.go()
Filtering on an element in a list
attribute
animals.update({animal: "tiger", name: "janet"})
.set({keeper: "Joe Exotic"})
.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)"
}