DynamoDB Java SDK - QueryRequest with IN clause

Question

The Javadoc of software.amazon.awssdk.services.dynamodb.model.QueryRequest.expressionAttributeValues() says a following query can be done:

ProductStatus IN (:avail, :back, :disc)

where the values are passed in in "ExpressionAttributeValues" as follows:

{ ":avail":{"S":"Available"}, ":back":{"S":"Backordered"}, ":disc":{"S":"Discontinued"} }

I'm now trying to figure how

QueryRequest.expressionAttributeValues is the following Map:

Map<String, AttributeValue> expressionAttributeValues)

there doesn't seem to be any other way to pass expression values to the expression defined in QueryRequest.keyConditionExpression()

I specifically looked for a way to pass in a JSON expression -- can't find any. skipped the legacy methods-- QueryRequest.keyConditions() and some others.

i tried

  QueryRequest queryRequest = QueryRequest
            .builder().tableName("tablename")
            .indexName("user-index")
            .keyConditionExpression("index-ID IN (:key0, :key1, :key2)"
            .expressionAttributeValues(keys)
            .build();

where Map<String, Map<String, AttributeValue>> keys looks like the following when i print this Map with its toString():

{:key2=AttributeValue(S=5555550), :key1=AttributeValue(S=5555551), :key0=AttributeValue(S=5555552)}

the following exception is thrown to this:

software.amazon.awssdk.services.dynamodb.model.DynamoDbException: ExpressionAttributeValues contains invalid key: Syntax error; key: "":key1"" (Service: DynamoDb, Status Code: 400, Request ID: R986SKT4JTK02C57HD0P8591TVQV4KQNSO5AEMVJF66Q9ASUAAJG)
    at software.amazon.awssdk.core.internal.http.CombinedResponseHandler.handleErrorResponse(CombinedResponseHandler.java:125)
    at software.amazon.awssdk.core.internal.http.CombinedResponseHandler.handleResponse(CombinedResponseHandler.java:82)
    at software.amazon.awssdk.core.internal.http.CombinedResponseHandler.handle(CombinedResponseHandler.java:60)
    at software.amazon.awssdk.core.internal.http.CombinedResponseHandler.handle(CombinedResponseHandler.java:41)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.HandleResponseStage.execute(HandleResponseStage.java:40)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.HandleResponseStage.execute(HandleResponseStage.java:30)
    at software.amazon.awssdk.core.internal.http.pipeline.RequestPipelineBuilder$ComposingRequestPipelineStage.execute(RequestPipelineBuilder.java:206)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.ApiCallAttemptTimeoutTrackingStage.execute(ApiCallAttemptTimeoutTrackingStage.java:73)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.ApiCallAttemptTimeoutTrackingStage.execute(ApiCallAttemptTimeoutTrackingStage.java:42)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.TimeoutExceptionHandlingStage.execute(TimeoutExceptionHandlingStage.java:78)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.TimeoutExceptionHandlingStage.execute(TimeoutExceptionHandlingStage.java:40)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.ApiCallAttemptMetricCollectionStage.execute(ApiCallAttemptMetricCollectionStage.java:50)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.ApiCallAttemptMetricCollectionStage.execute(ApiCallAttemptMetricCollectionStage.java:36)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.RetryableStage.execute(RetryableStage.java:81)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.RetryableStage.execute(RetryableStage.java:36)
    at software.amazon.awssdk.core.internal.http.pipeline.RequestPipelineBuilder$ComposingRequestPipelineStage.execute(RequestPipelineBuilder.java:206)
    at software.amazon.awssdk.core.internal.http.StreamManagingStage.execute(StreamManagingStage.java:56)
    at software.amazon.awssdk.core.internal.http.StreamManagingStage.execute(StreamManagingStage.java:36)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.ApiCallTimeoutTrackingStage.executeWithTimer(ApiCallTimeoutTrackingStage.java:80)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.ApiCallTimeoutTrackingStage.execute(ApiCallTimeoutTrackingStage.java:60)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.ApiCallTimeoutTrackingStage.execute(ApiCallTimeoutTrackingStage.java:42)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.ApiCallMetricCollectionStage.execute(ApiCallMetricCollectionStage.java:48)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.ApiCallMetricCollectionStage.execute(ApiCallMetricCollectionStage.java:31)
    at software.amazon.awssdk.core.internal.http.pipeline.RequestPipelineBuilder$ComposingRequestPipelineStage.execute(RequestPipelineBuilder.java:206)
    at software.amazon.awssdk.core.internal.http.pipeline.RequestPipelineBuilder$ComposingRequestPipelineStage.execute(RequestPipelineBuilder.java:206)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.ExecutionFailureExceptionReportingStage.execute(ExecutionFailureExceptionReportingStage.java:37)
    at software.amazon.awssdk.core.internal.http.pipeline.stages.ExecutionFailureExceptionReportingStage.execute(ExecutionFailureExceptionReportingStage.java:26)
    at software.amazon.awssdk.core.internal.http.AmazonSyncHttpClient$RequestExecutionBuilderImpl.execute(AmazonSyncHttpClient.java:193)
    at software.amazon.awssdk.core.internal.handler.BaseSyncClientHandler.invoke(BaseSyncClientHandler.java:103)
    at software.amazon.awssdk.core.internal.handler.BaseSyncClientHandler.doExecute(BaseSyncClientHandler.java:171)
    at software.amazon.awssdk.core.internal.handler.BaseSyncClientHandler.lambda$execute$1(BaseSyncClientHandler.java:82)
    at software.amazon.awssdk.core.internal.handler.BaseSyncClientHandler.measureApiCallSuccess(BaseSyncClientHandler.java:179)
    at software.amazon.awssdk.core.internal.handler.BaseSyncClientHandler.execute(BaseSyncClientHandler.java:76)
    at software.amazon.awssdk.core.client.handler.SdkSyncClientHandler.execute(SdkSyncClientHandler.java:45)
    at software.amazon.awssdk.awscore.client.handler.AwsSyncClientHandler.execute(AwsSyncClientHandler.java:56)
    at software.amazon.awssdk.services.dynamodb.DefaultDynamoDbClient.query(DefaultDynamoDbClient.java:4372)

i also tried surrounding the key-s with quotes along with some other variations:

{":key2"=AttributeValue(S=5555550), ":key1"=AttributeValue(S=5555551), ":key0"=AttributeValue(S=5555552)}

Will greatly appreciate the help. Wore me out.

//==================

UPDATE: i'm aware of the direct Restful call via a script to AWS server, but we're looking for the Java solution.

Answer 1

What you are trying to achieve is not possible for a Query using KeyConditionExpression.

A KeyConditionExpression requires an equals = comparative on a single partition key only, multiple keys using IN or OR or AND are now allowed.

Use the KeyConditionExpression parameter to provide a specific value for the partition key. The Query operation will return all of the items from the table or index with that partition key value. You can optionally narrow the scope of the Query operation by specifying a sort key value and a comparison operator in KeyConditionExpression.

ref

---

The only API that allows syntax similar to what you are creating is PartiQL ExecuteStatement where you can pass in up to 50 partition keys as part of an IN clause:

SELECT * FROM mytable WHERE PK IN [1,2...50]

https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_ExecuteStatement.html