Querying DynamoDB table by hash and range key

Question

I want to query DynamoDB table by hash and range key, using AWS SDK for Ruby V2. Following code can work.

dynamodb = Aws::DynamoDB::Client.new(region: 'somewhere')
dynamodb.query(
  table_name: TABLE_NAME,
  key_conditions: {
    HASH_KEY_NAME => {
      attribute_value_list: ['hoge'],
      comparison_operator: 'EQ'
    },
    RANGE_KEY_NAME => {
      attribute_value_list: ['foo'],
      comparison_operator: 'EQ'
    }
  }
)

But, I want to set multiple items to range key condition.

Like this:

dynamodb = Aws::DynamoDB::Client.new(region: 'somewhere')
dynamodb.query(
  table_name: TABLE_NAME,
  key_conditions: {
    HASH_KEY_NAME => {
      attribute_value_list: ['hoge'],
      comparison_operator: 'EQ'
    },
    RANGE_KEY_NAME => {
      attribute_value_list: ['foo', 'bar'],
      comparison_operator: 'EQ'
    }
  }
)

This code returns lib/ruby/gems/2.2.0/gems/aws-sdk-core-2.0.48/lib/seahorse/client/plugins/raise_response_errors.rb:15:in `call': One or more parameter values were invalid: Invalid number of argument(s) for the EQ ComparisonOperator (Aws::DynamoDB::Errors::ValidationException).

I've tried to use IN operator.

dynamodb = Aws::DynamoDB::Client.new(region: 'somewhere')
dynamodb.query(
  table_name: TABLE_NAME,
  key_conditions: {
    HASH_KEY_NAME => {
      attribute_value_list: ['hoge'],
      comparison_operator: 'EQ'
    },
    RANGE_KEY_NAME => {
      attribute_value_list: ['foo', 'bar'],
      comparison_operator: 'IN'
    }
  }
)

It returns lib/ruby/gems/2.2.0/gems/aws-sdk-core-2.0.48/lib/seahorse/client/plugins/raise_response_errors.rb:15:in `call': Attempted conditional constraint is not an indexable operation (Aws::DynamoDB::Errors::ValidationException).

How do I query DynamoDB table by one hash key and multiple range keys?

Answer 1

The Query operation only allows the following operators on the Range Key:

EQ | LE | LT | GE | GT | BEGINS_WITH | BETWEEN

For a Query operation, Condition is used for specifying the KeyConditions to use when querying a table or an index. For KeyConditions, only the following comparison operators are supported:

EQ | LE | LT | GE | GT | BEGINS_WITH | BETWEEN

Source: http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/QueryAndScan.html

You can still meet the requirements by using a FilterExpression :

:filter_expression => "RANGE_KEY_NAME in (:id1, :id2)",{ ":id1" => "hoge",":id2" => "foo"}

However the consumed provisioned throughput will be based on the query returned results rather than filtered result set.

Another option would be to send multiple GetItem requests (each one with a possible Range Key value) via BatchGetItem. The result would contain only the matching records:

resp = dynamodb.batch_get_item(
  # required
  request_items: {
    "TableName" => {
      # required
      keys: [
        {
          "AttributeName" => "value", #<Hash,Array,String,Numeric,Boolean,nil,IO,Set>,
        },
      ],
      attributes_to_get: ["AttributeName", '...'],
      consistent_read: true,
      projection_expression: "ProjectionExpression",
      expression_attribute_names: { "ExpressionAttributeNameVariable" => "AttributeName" },
    },
  },
  return_consumed_capacity: "INDEXES|TOTAL|NONE",
)

Source : http://docs.aws.amazon.com/sdkforruby/api/Aws/DynamoDB/Client.html