Amazon Web Services Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – AWS DynamoDB QueryRequest multiple sort key values

I have the following data

Partition key Sort key value
aa str1 1
aa str2 123
aa str3 122
ab str1 12
ab str3 111
ac str1 112

And by using QueryRequest I want to select entries where the partition key is "aa" and the sort key is either "str1" or "str3". I have tried to create a Condition, but nothing works with different exceptions, so could someone point out how this query can be written?

var pkCondition = new Condition
{
    ComparisonOperator = ComparisonOperator.EQ,
    AttributeValueList =
    {
        new AttributeValue { S = "aa" },
    },
};

// Exception One or more parameter values were invalid: Condition parameter type does not match schema type
var skCondition1 = new Condition
{
    ComparisonOperator = ComparisonOperator.EQ,
    AttributeValueList =
    {
        new AttributeValue { SS = { "str1", "str3" } },
    },
};

// Exception: One or more parameter values were invalid: Invalid number of argument(s) for the EQ ComparisonOperator
var skCondition2 = new Condition
{
    ComparisonOperator = ComparisonOperator.EQ,
    AttributeValueList =
    {
        new AttributeValue { S = "str1" },
        new AttributeValue { S = "str3" },
    },
};

// Well this one is clear because IN cannot be performed on keys
// Exception: One or more parameter values were invalid: ComparisonOperator IN is not valid for SS AttributeValue type
var skCondition3 = new Condition
{
    ComparisonOperator = ComparisonOperator.IN,
    AttributeValueList =
    {
        new AttributeValue { SS = { "str1", "str3" } },
    },
};

// Works, but not what I need
var skCondition4 = new Condition
{
    ComparisonOperator = ComparisonOperator.EQ,
    AttributeValueList =
    {
        new AttributeValue { S = "str1" },
    },
};

return new QueryRequest
{
    TableName = "My table",
    KeyConditions =
    {
        { "Partition key", pkCondition },
        { "Sort key", skCondition },
    },
};

After some research I have found out that KeyConditionExpression can also be used, but I have not found any combination that could work for my case.

// Exception: Invalid operator used in KeyConditionExpression: OR
const string keyConditionExpression1 = "#pk = :pkval AND (#sk = :sk1 OR #sk = :sk2)";

// Exception: Invalid operator used in KeyConditionExpression: IN
const string keyConditionExpression2 = "#pk = :pkval AND #sk IN (:sk1, :sk2)";

new QueryRequest
{
    TableName = "My table",
    KeyConditionExpression = keyConditionExpression,
    ExpressionAttributeNames = new Dictionary<string, string>
    {
        { "#pk", "Partition key" },
        { "#sk", "Sort key" },
    },
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>
    {
        { ":pkval", new AttributeValue { S = "aa" } },
        { ":sk1", new AttributeValue { S = "str1" } },
        { ":sk2", new AttributeValue { S = "str3" } },
    },
};

Next, I have also tried using FilterExpression where IN and OR are allowed, but again there was another exception: Filter Expression can only contain non-primary key attributes.

new QueryRequest
{
    TableName = "My table",
    KeyConditionExpression = "#pk = :pkval",
    FilterExpression = "#sk IN (:sk1, :sk2)",
    ExpressionAttributeNames = new Dictionary<string, string>
    {
        { "#pk", "Partition key" },
        { "#sk", "Sort key" },
    },
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>
    {
        { ":pkval", new AttributeValue { S = "aa" } },
        { ":sk1", new AttributeValue { S = "str1" } },
        { ":sk2", new AttributeValue { S = "str3" } },
    },
};
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Based on Leeroy's answer there is a C# code that can make the request:

    var keyAttributes = new KeysAndAttributes
{
    Keys =
    {
        new Dictionary<string, AttributeValue>
        {
            { "Partition key", new AttributeValue { S = "aa" } },
            { "Sort key", new AttributeValue { S = "str1" } },
        },
        new Dictionary<string, AttributeValue>
        {
            { "Partition key", new AttributeValue { S = "aa" } },
            { "Sort key", new AttributeValue { S = "str3" } },
        },
    },
};

return new BatchGetItemRequest
{
    RequestItems =
    {
        { "My table", keyAttributes },
    },
};

  2. 0

    DynamoDB data structure can be likened to a B-tree. That is to say that an efficient Query reads only a contiguous piece of data.

    You have an access pattern where you cannot guarantee that the data is contiguous and stored beside each other on disk. It’s for that reason you are struggling.

    Possible Solutions

    • Depending on the actual data you store in your sort key, you can do a between or begins_with condition. `WHERE SK between(str1,str3).

    • However your access pattern seems to suggest that you have specific sort keys you need that do not follow a pattern, in which case you should us a BatchGetItem which will allow you to fetch up to 100 items per request.

    • If the full value of the sort key is unknown, then you can do an ExecuteStatement request which will execute multiple Query calls under the hood fulfilling your access pattern.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search