Summary
DynamoDB supports sk BETWEEN :low AND :high in KeyConditionExpression for sort keys. This is useful for range queries (e.g., date ranges, numeric ranges).
Current Behavior
Users can already express BETWEEN semantics via sk >= low and sk <= high in Ash filters — no new Ash function or fragment is needed. However, the current implementation has suboptimal handling:
partition_predicates/2 removes both SK predicates from filter_preds (since they reference a key attribute)
build_key_condition/4 calls fetch_key_value/2 which uses Enum.find_value — it only captures the first SK predicate for KeyConditionExpression
- The second SK predicate is not in
KeyConditionExpression and not in FilterExpression — it's only caught by runtime filtering via apply_runtime_filter/2
This means queries like sk >= "2024-01-01" and sk <= "2024-12-31" currently:
- Push only one condition (e.g.,
sk >= "2024-01-01") to KeyConditionExpression
- Apply the other (e.g.,
sk <= "2024-12-31") as a runtime filter (client-side)
- Return correct results, but read more items from DynamoDB than necessary
Recommended Implementation
- Detect complementary SK predicates — in
build_key_condition/4, find two predicates on the same sort key with >= and <= operators, and emit #sk BETWEEN :v_sk_low AND :v_sk_high in KeyConditionExpression
- No new Ash function needed —
BETWEEN maps to existing >= and <= operators that Ash already supports
- Fallback — when only one SK predicate exists, keep the current single-operator behavior
Current vs. optimized behavior
Post
|> Ash.Query.filter(partition_key == "pk1" and sort_key >= "2024-01-01" and sort_key <= "2024-12-31")
|> Ash.read!()
Currently generates: KeyConditionExpression: "#pk = :v_pk AND #sk >= :v_sk" (second condition is runtime-only)
Would generate: KeyConditionExpression: "#pk = :v_pk AND #sk BETWEEN :v_sk_low AND :v_sk_high"
Summary
DynamoDB supports
sk BETWEEN :low AND :highinKeyConditionExpressionfor sort keys. This is useful for range queries (e.g., date ranges, numeric ranges).Current Behavior
Users can already express
BETWEENsemantics viask >= low and sk <= highin Ash filters — no new Ash function or fragment is needed. However, the current implementation has suboptimal handling:partition_predicates/2removes both SK predicates fromfilter_preds(since they reference a key attribute)build_key_condition/4callsfetch_key_value/2which usesEnum.find_value— it only captures the first SK predicate forKeyConditionExpressionKeyConditionExpressionand not inFilterExpression— it's only caught by runtime filtering viaapply_runtime_filter/2This means queries like
sk >= "2024-01-01" and sk <= "2024-12-31"currently:sk >= "2024-01-01") toKeyConditionExpressionsk <= "2024-12-31") as a runtime filter (client-side)Recommended Implementation
build_key_condition/4, find two predicates on the same sort key with>=and<=operators, and emit#sk BETWEEN :v_sk_low AND :v_sk_highinKeyConditionExpressionBETWEENmaps to existing>=and<=operators that Ash already supportsCurrent vs. optimized behavior
Currently generates:
KeyConditionExpression: "#pk = :v_pk AND #sk >= :v_sk"(second condition is runtime-only)Would generate:
KeyConditionExpression: "#pk = :v_pk AND #sk BETWEEN :v_sk_low AND :v_sk_high"