如何使用AWS DynamoDB QueryRequest查询多排序键值？

现有数据 #

需求 #

使用QueryRequest筛选出**Partition key为"aa"且Sort key为"str1"或"str3"**的条目。

尝试的错误写法及异常 #

1. 使用Condition的错误尝试 #

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

// 异常：一个或多个参数值无效：条件参数类型与架构类型不匹配
var skCondition1 = new Condition
{
    ComparisonOperator = ComparisonOperator.EQ,
    AttributeValueList =
    {
        new AttributeValue { SS = { "str1", "str3" } },
    },
};

// 异常：一个或多个参数值无效：EQ比较运算符的参数数量无效
var skCondition2 = new Condition
{
    ComparisonOperator = ComparisonOperator.EQ,
    AttributeValueList =
    {
        new AttributeValue { S = "str1" },
        new AttributeValue { S = "str3" },
    },
};

// 异常：一个或多个参数值无效：IN比较运算符不适用于SS类型AttributeValue
var skCondition3 = new Condition
{
    ComparisonOperator = ComparisonOperator.IN,
    AttributeValueList =
    {
        new AttributeValue { SS = { "str1", "str3" } },
    },
};

// 可运行但不符合需求（仅筛选出Sort key为str1的条目）
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 },
    },
};

2. 使用KeyConditionExpression的错误尝试 #

// 异常：KeyConditionExpression中使用了无效运算符：OR
const string keyConditionExpression1 = "#pk = :pkval AND (#sk = :sk1 OR #sk = :sk2)";

// 异常：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" } },
    },
};

3. 使用FilterExpression的错误尝试 #

// 异常：筛选表达式只能包含非主键属性
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" } },
    },
};

正确解决方案 #

DynamoDB的Query操作对主键有严格限制：KeyConditionExpression只能对排序键使用单一等值匹配，或连续范围匹配（如BETWEEN、>、<等），无法直接用OR/IN指定多个排序键值，且FilterExpression不能作用于主键属性。针对需求，有两种可行方案：

方案1：执行两次Query，合并结果集 #

分别针对aa+str1和aa+str3发起Query请求，再合并结果：

// 第一次Query：筛选Partition key=aa，Sort key=str1
var query1 = new QueryRequest
{
    TableName = "My table",
    KeyConditionExpression = "#pk = :pkval AND #sk = :sk1",
    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" } }
    }
};

// 第二次Query：筛选Partition key=aa，Sort key=str3
var query2 = new QueryRequest
{
    TableName = "My table",
    KeyConditionExpression = "#pk = :pkval AND #sk = :sk3",
    ExpressionAttributeNames = new Dictionary<string, string>
    {
        { "#pk", "Partition key" },
        { "#sk", "Sort key" }
    },
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>
    {
        { ":pkval", new AttributeValue { S = "aa" } },
        { ":sk3", new AttributeValue { S = "str3" } }
    }
};

// 执行查询并合并结果
var result1 = await dynamoDbClient.QueryAsync(query1);
var result2 = await dynamoDbClient.QueryAsync(query2);
var combinedItems = result1.Items.Concat(result2.Items).ToList();

方案2：使用BatchGetItemRequest（适用于已知完整主键的场景） #

如果明确知道要查询的完整主键对，可一次性批量获取：

var batchRequest = new BatchGetItemRequest
{
    RequestItems = new Dictionary<string, KeysAndAttributes>
    {
        {
            "My table",
            new KeysAndAttributes
            {
                Keys = new List<Dictionary<string, AttributeValue>>
                {
                    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" } }
                    }
                }
            }
        }
    }
};

var batchResult = await dynamoDbClient.BatchGetItemAsync(batchRequest);
var items = batchResult.Responses["My table"];

原因说明 #

• DynamoDB的Query基于主键索引实现，要求排序键条件为单一等值或连续范围，多值匹配（OR/IN）不符合索引的有序存储逻辑。
• FilterExpression仅用于过滤非主键属性，无法对分区键或排序键生效。

内容的提问来源于stack exchange，提问作者mihails.kuzmins