Amazon DyanamoDB ,Using filter expressions with scan operations in Java

Question

I am trying to get all the items whose price is greater than a certain value,but not able to use the filter expressions correctly.

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import com.amazonaws.auth.profile.ProfileCredentialsProvider;
import com.amazonaws.regions.Region;
import com.amazonaws.regions.Regions;
import com.amazonaws.services.dynamodbv2.AmazonDynamoDBClient;
import com.amazonaws.services.dynamodbv2.document.DynamoDB;
import com.amazonaws.services.dynamodbv2.document.Item;
import com.amazonaws.services.dynamodbv2.document.ItemCollection;
import com.amazonaws.services.dynamodbv2.document.QueryOutcome;
import com.amazonaws.services.dynamodbv2.document.Table;
import com.amazonaws.services.dynamodbv2.document.spec.QuerySpec;
import com.amazonaws.services.dynamodbv2.model.AttributeValue;
import com.amazonaws.services.dynamodbv2.model.PutItemRequest;
import com.amazonaws.services.dynamodbv2.model.ScanRequest;
import com.amazonaws.services.dynamodbv2.model.ScanResult;

public class QuerySample {
    // Setting up the client

    static AmazonDynamoDBClient db = new AmazonDynamoDBClient(
            new ProfileCredentialsProvider());
    // Setting up the DB
    static DynamoDB dynamoDB = new DynamoDB(db);

    public static void main(String a[]) {
        // Setting up the Region
        Region usWest = Region.getRegion(Regions.US_WEST_2);
        db.setRegion(usWest);
        Table table = dynamoDB.getTable("Thread");
        SimpleDateFormat dt = new SimpleDateFormat("yyyy-mm-dd HH:mm:ss.SSS");
        long time = (new Date()).getTime();
        Date date = new Date();
        date.setTime(time);
        System.out.println("The date is " + date);
        // ScanRequest scanRequest = new ScanRequest()
        // .withTableName("sys_ping");
        // ScanResult result = db.scan(scanRequest);
        // for (Map<String, AttributeValue> item : result.getItems()){
        // System.out.println(item);
        // }

        Map<String, Object> expressionAttributeValues = new HashMap<String, Object>();
        expressionAttributeValues.put(":val", "19");
        expressionAttributeValues.put(":val1",
                new AttributeValue().withN("2000"));
        ScanRequest scanRequest = new ScanRequest().withTableName(
                "ProductCatalog").withFilterExpression("Price >= :val");
        ScanResult result = db.scan(scanRequest);
        for (Map<String, AttributeValue> item : result.getItems()) {
            System.out.println(item);

        }

    }
}

It throws the following runtime exception

Exception in thread "main" com.amazonaws.AmazonServiceException: Invalid FilterExpression: An expression attribute value used in expression is not defined; attribute value: :val (Service: AmazonDynamoDBv2; Status Code: 400; Error Code: ValidationException; Request ID: FEQBP55SPJIT60JVFPVO6N6BLBVV4KQNSO5AEMVJF66Q9ASUAAJG)

Answer 1

    // solution simplified

    public List findAllByKeyValue(String key, String value) {
            String keyWithHash = String.format("#%s", key);
            AttributeValue attributeValue = AttributeValue.builder().s(value).build();

            Expression expression = Expr`enter code here`ession.builder()
                    .expressionValues(Collections.singletonMap(":val1", attributeValue))
                    .expressionNames(Collections.singletonMap(keyWithHash, key))
                    .expression(String.format("%s = :val1", keyWithHash))
                    .build();
            ScanEnhancedRequest enhancedRequest =
                    ScanEnhancedRequest.builder().filterExpression(expression).build();

            return getTable().scan(enhancedRequest).items().stream().toList();
    }

Answer 2

Here is the Java example for DynamoDB API V2. Assume we have a table named Work and a column named Archive. The Archive column can be Closed or Open.

Now assume, we want to query only Open items. Using the Java v2 and the Enhanced Client, we can do this query as follows.

package com.example.dynamodb;

// snippet-start:[dynamodb.java2.mapping.scanEx.import]
import java.time.Instant;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import software.amazon.awssdk.enhanced.dynamodb.DynamoDbEnhancedClient;
import software.amazon.awssdk.enhanced.dynamodb.DynamoDbTable;
import software.amazon.awssdk.enhanced.dynamodb.Expression;
import software.amazon.awssdk.enhanced.dynamodb.TableSchema;
import software.amazon.awssdk.enhanced.dynamodb.mapper.annotations.DynamoDbBean;
import software.amazon.awssdk.enhanced.dynamodb.mapper.annotations.DynamoDbPartitionKey;
import software.amazon.awssdk.enhanced.dynamodb.mapper.annotations.DynamoDbSortKey;
import software.amazon.awssdk.enhanced.dynamodb.model.ScanEnhancedRequest;
import software.amazon.awssdk.regions.Region;
import software.amazon.awssdk.services.dynamodb.DynamoDbClient;
import software.amazon.awssdk.services.dynamodb.model.AttributeValue;
import software.amazon.awssdk.services.dynamodb.model.DynamoDbException;
// snippet-end:[dynamodb.java2.mapping.scanEx.import]

/*
    This code example uses an Expression object to select only Open items for the archive column.
    Prior to running this code example, create a Work table that contains the following fields:

    1.  id - Represents the key.
    2. date - Specifies the date the item was created.
    3. description - A value that describes the item.
    4. guide - A value that represents the deliverable being worked on.
    5. status - A value that describes the status.
    6. username - A value that represents the user who entered the item.
    7. archive - A value that represents whether this is an active or archive item. Specify Open and Closed items.
 */

public class EnhancedScanRecordsWithExpression {
    // Query the Record table
    public static void main(String[] args) {

        //Create a DynamoDbClient object
        Region region = Region.US_EAST_1;
        DynamoDbClient ddb = DynamoDbClient.builder()
                .region(region)
                .build();
        // Create a DynamoDbEnhancedClient and use the DynamoDbClient object
        DynamoDbEnhancedClient enhancedClient = DynamoDbEnhancedClient.builder()
                .dynamoDbClient(ddb)
                .build();

        scan(enhancedClient);
    }

    // snippet-start:[dynamodb.java2.mapping.scanEx.main]
    public static void scan(DynamoDbEnhancedClient enhancedClient) {

        try {
            //Create a DynamoDbTable object
            DynamoDbTable<Work> table = enhancedClient.table("Work", TableSchema.fromBean(Work.class));

            AttributeValue attr = AttributeValue.builder()
                    .s("Open")
                    .build();

            // Get only Open items in the Work table
            Map<String, AttributeValue> myMap = new HashMap<>();
            myMap.put(":val1", attr);

            Map<String, String> myExMap = new HashMap<>();
            myExMap.put("#archive", "archive");

            // Set the Expression so only Closed items are queried from the Work table
            Expression expression = Expression.builder()
                    .expressionValues(myMap)
                    .expressionNames(myExMap)
                    .expression("#archive = :val1")
                    .build();

            ScanEnhancedRequest enhancedRequest = ScanEnhancedRequest.builder()
                    .filterExpression(expression)
                    .limit(15)
                    .build();

            // Get items in the Record table and write out the ID value
            Iterator<Work> results = table.scan().items().iterator();

            while (results.hasNext()) {

                Work rec = results.next();
                System.out.println("The record id is " + rec.getId());
            }

        } catch (DynamoDbException e) {
            System.err.println(e.getMessage());
            System.exit(1);
        }
        System.out.println("Done");
    }
    // snippet-end:[dynamodb.java2.mapping.scanEx.main]
}

Answer 3

You are misusing the API. Look at Map<String, Object> expressionAttributeValues

1. There is both :val and :val1 there.
2. You are not passing it to the new ScanRequest()