如何用PartiQL在DynamoDB的Geocache中筛选含BR的short_name

需求 #

要检索short_name字段值为"BR"的Geocache记录，以此筛选巴西的相关数据。但查阅PartiQL文档后发现，不少PartiQL特性在DynamoDB中不支持，再加上当前Geocache的存储结构，给数据筛选带来了麻烦。

当前执行的查询语句 #

SELECT googleResult.address_components FROM Geocache

查询返回结果 #

返回的address_components结构如下：

"address_components": {
    "L": [
        {
            "M": {
                "long_name": {
                    "S": "803"
                },
                "short_name": {
                    "S": "803"
                },
                "types": {
                    "L": [
                        {
                            "S": "street_number"
                        }
                    ]
                }
            }
        },
        {
            "M": {
                "long_name": {
                    "S": "Rua Sócrates"
                },
                "short_name": {
                    "S": "R. Sócrates"
                },
                "types": {
                    "L": [
                        {
                            "S": "route"
                        }
                    ]
                }
            }
        },
        {
            "M": {
                "long_name": {
                    "S": "Jardim Marajoara"
                },
                "short_name": {
                    "S": "Jardim Marajoara"
                },
                "types": {
                    "L": [
                        {
                            "S": "political"
                        },
                        {
                            "S": "sublocality"
                        },
                        {
                            "S": "sublocality_level_1"
                        }
                    ]
                }
            }
        },
        {
            "M": {
                "long_name": {
                    "S": "São Paulo"
                },
                "short_name": {
                    "S": "São Paulo"
                },
                "types": {
                    "L": [
                        {
                            "S": "administrative_area_level_2"
                        },
                        {
                            "S": "political"
                        }
                    ]
                }
            }
        },
        {
            "M": {
                "long_name": {
                    "S": "São Paulo"
                },
                "short_name": {
                    "S": "SP"
                },
                "types": {
                    "L": [
                        {
                            "S": "administrative_area_level_1"
                        },
                        {
                            "S": "political"
                        }
                    ]
                }
            }
        },
        {
            "M": {
                "long_name": {
                    "S": "Brasil"
                },
                "short_name": {
                    "S": "BR"
                },
                "types": {
                    "L": [
                        {
                            "S": "country"
                        },
                        {
                            "S": "political"
                        }
                    ]
                }
            }
        },
        {
            "M": {
                "long_name": {
                    "S": "04671-205"
                },
                "short_name": {
                    "S": "04671-205"
                },
                "types": {
                    "L": [
                        {
                            "S": "postal_code"
                        }
                    ]
                }
            }
        }
    ]
}

可行解决方案 #

由于DynamoDB的PartiQL不支持复杂数组过滤，推荐两种处理方式：

方案一：使用DynamoDB Scan操作加过滤表达式 #

通过FilterExpression结合数组遍历，筛选出address_components中存在short_name.S = 'BR'的记录：

Map<String, AttributeValue> expressionAttributeValues = new HashMap<>();
expressionAttributeValues.put(":br", AttributeValue.builder().s("BR").build());

ScanRequest scanRequest = ScanRequest.builder()
        .tableName("Geocache")
        .filterExpression("contains(googleResult.address_components[*].short_name.S, :br)")
        .expressionAttributeValues(expressionAttributeValues)
        .build();

方案二：调整数据存储结构 #

将国家短码（如country_short_code）单独提取为顶级字段，之后可直接用PartiQL高效筛选：

SELECT * FROM Geocache WHERE country_short_code = 'BR'

这种方式能避免全表扫描，大幅提升查询性能。

内容的提问来源于stack exchange，提问作者Lucas Fernandes