如何在DynamoDB中查询不存在的（null）属性 [英] How do you query for a non-existent (null) attribute in DynamoDB

问题描述

我正在尝试查询DynamoDB表，以查找未设置电子邮件属性的所有项目。包含 email 字段的表上存在一个名为 EmailPasswordIndex 的全局二级索引。

I'm trying to query a DynamoDB table to find all items where the email attribute is not set. A global secondary index called EmailPasswordIndex exists on the table which includes the email field.

var params = {
    "TableName": "Accounts",
    "IndexName": "EmailPasswordIndex",
    "KeyConditionExpression": "email = NULL",
};

dynamodb.query(params, function(err, data) {
    if (err)
        console.log(JSON.stringify(err, null, 2));
    else
        console.log(JSON.stringify(data, null, 2));
});

结果：

{
  "message": "Invalid KeyConditionExpression: Attribute name is a reserved keyword; reserved keyword: NULL",
  "code": "ValidationException",
  "time": "2015-12-18T05:33:00.356Z",
  "statusCode": 400,
  "retryable": false
}

表定义：

var params = {
    "TableName": "Accounts",
    "KeySchema": [
        { "AttributeName": "id", KeyType: "HASH" }, // Randomly generated UUID
    ],
    "AttributeDefinitions": [
        { "AttributeName": "id", AttributeType: "S" },
        { "AttributeName": "email", AttributeType: "S" }, // User e-mail.
        { "AttributeName": "password", AttributeType: "S" }, // Hashed password.
    ],
    "GlobalSecondaryIndexes": [
        {
            "IndexName": "EmailPasswordIndex",
            "ProvisionedThroughput": {
                "ReadCapacityUnits": 1,
                "WriteCapacityUnits": 1
            },
            "KeySchema": [
                { "AttributeName": "email", KeyType: "HASH" },
                { "AttributeName": "password", KeyType: "RANGE" },
            ],
            "Projection": { "ProjectionType": "ALL" }
        },
    ],
    ProvisionedThroughput: {       
        ReadCapacityUnits: 1, 
        WriteCapacityUnits: 1
    }
};

dynamodb.createTable(params, function(err, data) {
    if (err)
        console.log(JSON.stringify(err, null, 2));
    else
        console.log(JSON.stringify(data, null, 2));
});

推荐答案

DynamoDB的全局二级索引允许稀疏索引。这意味着，如果您有一个GSI，但未定义该项目的哈希或范围键，则该项目将不会直接包含在GSI中。这在许多用例中很有用，因为它使您可以直接识别包含某些字段的记录。但是，如果您正在寻找缺少字段的方法，这种方法将行不通。

DynamoDB's Global Secondary Indexes allow for the indexes to be sparse. That means that if you have a GSI whose hash or range key for an item is not defined then that item will simply not be included in the GSI. This is useful in a number of use cases as it allows you to directly identify records that contain certain fields. However, this approach will not work if you are looking for the lack of a field.

要获得所有未设置字段的项目，最好的选择是借助过滤器进行扫描。此操作将非常昂贵，但代码很简单，如下所示：

To get all of the items that have a field not set your best bet may be resorting to a scan with a filter. This operation will be very expensive but it would be straightforward code looking something like the following:

var params = {
    TableName: "Accounts",
    FilterExpression: "attribute_not_exists(email)"
};

dynamodb.scan(params, {
    if (err)
        console.log(JSON.stringify(err, null, 2));
    else
        console.log(JSON.stringify(data, null, 2));
});

这篇关于如何在DynamoDB中查询不存在的（null）属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！