Adding a where clause to AWS DynamoDB

Question

I am trying to create a getItem request in AWS Lambda to access DynamoDB like so:

dynamodb.getItem({
    TableName: "DataTable",
    Key: {
        user: {
            S: user
        },
        deleted: {
            BOOL: false
        }
    }
}, function(err, data) {
    if (err) return fn(err);
    else {
        if ('Item' in data) {
            fn(null, user);
        } else {
            fn(null, null); // User not found
        }
    }
});

It worked fine when I passed the user in as that was the primary key on the table. I added a deleted boolean to create a soft delete on users. But one I added that in the schema errors started to happen as deleted isn't part of the primary key. I want a way to add it as a where clause coming from the relational DB world. How is this done? Thanks. :o)

notionquest notionquest · Accepted Answer · 2016-10-31T11:54:38

The getItem cannot be used if the data has to be filtered by any non-key attributes.

I think in the above case, the 'deleted'attribute is a non-key attribute. So, the Query API should be used to filter the data along with key attribute.

Please refer the FilterExpression in the below example.

FilterExpression : 'deleted = :createdate'

(AWS.Request) query(params = {}, callback)

Sample code:-

var params = {
    TableName : table,
    KeyConditionExpression : 'yearkey = :hkey and title = :rkey',
    FilterExpression : 'deleted = :deleted',
    ExpressionAttributeValues : {
        ':hkey' : year_val,
        ':rkey' : title,
        ':deleted' : {BOOL : false}
    }
};

docClient.query(params, function(err, data) {
    if (err) {
        console.error("Unable to read item. Error JSON:", JSON.stringify(err,
                null, 2));
    } else {
        console.log("GetItem succeeded:", JSON.stringify(data, null, 2));
    }
});

For your use case, the key, filter condition and expression attribute value should be as mentioned below:-

KeyConditionExpression : 'user = :user',
FilterExpression : 'deleted = :deleted',
ExpressionAttributeValues : {
     ':user' : 'John',
     ':deleted' : {BOOL : false}
}

Adding a where clause to AWS DynamoDB

1 Answers