Error: DynamoDB scan filter expression not working

A DynamoDB Scan FilterExpression must use DynamoDB expression syntax, not SQL or legacy ScanFilter operator names. Functions such as contains and begins_with are case-sensitive. Bind names and values separately, then check pagination before concluding that the filter missed data.

import { DynamoDBClient } from "@aws-sdk/client-dynamodb";
import { DynamoDBDocumentClient, ScanCommand } from "@aws-sdk/lib-dynamodb";
const db = DynamoDBDocumentClient.from(new DynamoDBClient({ region: "us-east-1" }));
const page = await db.send(new ScanCommand({
  TableName: "Orders",
  FilterExpression: "(#status = :open OR #status = :pending) AND #total >= :minimum",
  ExpressionAttributeNames: { "#status": "status", "#total": "total" },
  ExpressionAttributeValues: { ":open": "OPEN", ":pending": "PENDING", ":minimum": 100 }
}));

This is one page and assumes total is a Number. Continue with page.LastEvaluatedKey as ExclusiveStartKey while it is present. With a low-level client, values require wrappers such as {"N":"100"}; do not mix those with document-client values.

• Use begins_with or contains instead of SQL LIKE. Use attribute_exists, attribute_not_exists, or attribute_type for the intended missing/null test.
• Use parentheses around mixed AND/OR groups. Alias reserved words and special attribute names.
• Treat a dotted document path differently from an attribute whose literal name contains a dot.
• A scan filter does not need an indexed attribute and does not save capacity for already-read items. Use a key-based Query when the access pattern supports it.

See filter expression builder and AWS documentation.