如何用DynamoDB QueryInput按数组内对象过滤数据

问题背景 #

需要通过DynamoDB的QueryInput方式过滤数据，当前已实现按Username字段过滤，但不知道如何过滤Goals数组内对象的BaseCategoryId和SubCategoryId属性。

数据结构 #

[
    {
        "Pk": "mimzeslami@gmail.com",
        "Sk": "social-shared-goal-2022-09-27 12:29:27",
        "Gsi1Pk": "social-shared-goal",
        "Gsi1Sk": "2022-09-27 12:29:27",
        "Username": "test@gmail.com",
        "Goals": [
            {
                "M": {
                    "Gsi2Sk": {
                        "S": "goal-end-2022-11-26 20:30:00"
                    },
                    "Gsi1Sk": {
                        "S": "goal-start-2022-09-27 12:28:47"
                    },
                    "Pk": {
                        "S": "mimzeslami@gmail.com"
                    },
                    "Gsi1Pk": {
                        "S": "mimzeslami@gmail.com"
                    },
                    "BaseCategoryId": {
                        "S": "85j85nachallll9idja"
                    },
                    "SubCategoryId": {
                        "S": "49023842874xhhiayx"
                    },
                    "Gsi2Pk": {
                        "S": "mimzeslami@gmail.com"
                    }
                }
            }
        ]
    }
]

已实现的Username过滤代码 #

keyCondition := map[string]*dynamodb.Condition{
    Gsi1Pk: {
        ComparisonOperator: aws.String(Eq),
        AttributeValueList: []*dynamodb.AttributeValue{
            {
                S: aws.String(constants.SocialSharedGoal),
            },
        },
    },
}
var queryFilter = map[string]*dynamodb.Condition{}
if maybeUserName != "" {
    queryFilter["Username"] = &dynamodb.Condition{
        ComparisonOperator: aws.String("CONTAINS"),
        AttributeValueList: []*dynamodb.AttributeValue{
            {
                S: aws.String(maybeUserName),
            },
        },
    }
}

params := &dynamodb.QueryInput{
    KeyConditions:    keyCondition,
    TableName:        aws.String(myTable),
    IndexName:        aws.String(Gsi1PkGsi1SkIndex),
    Limit:            &limit,
    ScanIndexForward: &ascSort,
    QueryFilter:      queryFilter,
}

解决方案：过滤数组内对象的属性 #

要过滤Goals数组中对象的BaseCategoryId和SubCategoryId，需要使用DynamoDB的路径表达式定位数组内的嵌套属性，结合CONTAINS操作符实现过滤。路径表达式中[*]表示匹配数组中的所有元素，再逐层定位到目标属性的具体值类型节点。

修改后的完整代码如下：

keyCondition := map[string]*dynamodb.Condition{
    Gsi1Pk: {
        ComparisonOperator: aws.String(Eq),
        AttributeValueList: []*dynamodb.AttributeValue{
            {
                S: aws.String(constants.SocialSharedGoal),
            },
        },
    },
}
var queryFilter = map[string]*dynamodb.Condition{}

// 按Username过滤
if maybeUserName != "" {
    queryFilter["Username"] = &dynamodb.Condition{
        ComparisonOperator: aws.String("CONTAINS"),
        AttributeValueList: []*dynamodb.AttributeValue{
            {
                S: aws.String(maybeUserName),
            },
        },
    }
}

// 按Goals数组中的BaseCategoryId过滤
if maybeBaseCategoryId != "" {
    queryFilter["Goals[*].M.BaseCategoryId.S"] = &dynamodb.Condition{
        ComparisonOperator: aws.String("CONTAINS"),
        AttributeValueList: []*dynamodb.AttributeValue{
            {
                S: aws.String(maybeBaseCategoryId),
            },
        },
    }
}

// 按Goals数组中的SubCategoryId过滤
if maybeSubCategoryId != "" {
    queryFilter["Goals[*].M.SubCategoryId.S"] = &dynamodb.Condition{
        ComparisonOperator: aws.String("CONTAINS"),
        AttributeValueList: []*dynamodb.AttributeValue{
            {
                S: aws.String(maybeSubCategoryId),
            },
        },
    }
}

params := &dynamodb.QueryInput{
    KeyConditions:    keyCondition,
    TableName:        aws.String(myTable),
    IndexName:        aws.String(Gsi1PkGsi1SkIndex),
    Limit:            &limit,
    ScanIndexForward: &ascSort,
    QueryFilter:      queryFilter,
}

说明 #

• 路径Goals[*].M.BaseCategoryId.S对应数据结构层级：Goals数组 → 数组元素（M类型对象） → BaseCategoryId字段 → S类型字符串值。
• 如果需要精确匹配而非包含匹配，可将CONTAINS替换为EQ。

内容的提问来源于stack exchange，提问作者Mohammadreza eslami