CODEWITHSUNDEEP
amazon-web-servicesamazon-dynamodbaws-sdknosql
Hadrien Pierre Mazelier
Hadrien Pierre Mazelier

Reputation: 474

"IN" statement in dynamodb

I have a "Users" table, here is a sample : 

{
    username:"haddox",
    formattedPhoneNumber:"676767676",
    verified: 0,
}

My wish is to retrieve all users whose formattedPhoneNumber is contained in an array of phone numbers (retrieved from my contacts). I created a secondary index, with verified as HASH and formattedPhoneNumber as RANGE. Here is my try : 

var params = {
    TableName: "Users",
    IndexName: "FormattedPhoneSecondaryIndex",
    KeyConditionExpression: "verified  = :v AND formattedPhone IN :n",
    ExpressionAttributeValues: {
        ":v":1,
        ":n": ["672053916", "642117296"]
    },
    ProjectionExpression: "username, formattedPhoneNumber"
};



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

But I get the following error : Invalid KeyConditionExpression: Syntax error; token: \":n\", near: \"IN :n\"",

Is there something wrong with the IN keyword ? Maybe there is another way to achieve this ?

Upvotes: 26

Views: 44906

Answers (2)

Min Soe
Min Soe

Reputation: 1254

This is how we solved. 

-(AWSDynamoDBScanExpression *) prepareScanExpressionWithName:(NSString*)name andValues:(NSArray *)vals {

AWSDynamoDBScanExpression *scanExpression = [AWSDynamoDBScanExpression new];

NSMutableString* filterExpression = [NSMutableString string];

NSMutableDictionary* expression = [NSMutableDictionary dictionary];

for(int i = 0; i < vals.count; i++)
    NSString *val = vals[i];
    NSString* key = [NSString stringWithFormat:@":val%i",i];

    [filterExpression appendString:key];
    [expression setObject:val forKey:key];

    if (i < vals.count) {
        [filterExpression appendString:@","];
    }
}

scanExpression.filterExpression = [NSString stringWithFormat:@"#P IN (%@)", filterExpression];
scanExpression.expressionAttributeNames = @{@"#P": name};
scanExpression.expressionAttributeValues = expression;
return scanExpression;
}

Upvotes: 0

Jeffrey Nieh
Jeffrey Nieh

Reputation: 346

KeyConditionExpression's cannot use the "IN" operator (see http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/QueryAndScan.html#FilteringResults). The idea with KeyConditions/KeyConditionExpression in a query operation is to more efficiently read pages of items from DynamoDB, since items with the same hash key but different range keys are stored contiguously and in sorted order. The IN operator would require extracting small portions of certain pages, which makes the Query operation less efficient, so it is not allowed in KeyConditions. You would want to add that as a FilterExpression instead, which is a convenience parameter to reduce the number of items returned from DynamoDB, but does not impact how the data is read from DynamoDB.

Upvotes: 20

Related Questions