Query DynamoDB with Two Matching Fileds

Question

I am new into DynamoDB. I am trying to query a collection with two matching field. I have written a code in mongoDB, I am trying to migrate to DocumentDB. I am facing issue.

MongoDB Code

This works well

getUser= async(req,res)=>{
let user = await user.findOne({phone:123456789, otp:2345});
}

DynamoDB Code

getUser= async(req,res)=>{
const params = {
         KeyConditionExpression: 'phone = :phone and #otp = :otp',
            ExpressionAttributeValues: {
                ':phone': 919600923917,
                ":otp":2387
            },
    TableName: "users",
    };
const user= await documentClient.query(params).promise();
}

Issue: Invalid KeyConditionExpression: An expression attribute name used in the document path is not defined; attribute name: #otp

Answer 1

As your error is shown

Issue: Invalid KeyConditionExpression: An expression attribute name used in the document path is not defined; attribute name: #otp

It simply means you add #otp = :otp in KeyConditionExpression, it should be not there in KeyConditionExpression. otp = :otp do something like in KeyConditionExpression.

Updated Answer:

As mentioned, the attribute included in "KeyConditionExpression" should be your hash key only, matching your base table schema (in this case 'phone' maybe). If you want to query on both 'phone' and 'otp', you need to create the table with hash and range key and specify 'otp' as your range key.

Global secondary indexes are completely separate from the base table, so in this case you can query the indexes separately (use 'otp' in key condition when querying OtpIndex).

Sample Code:

var params = {
        TableName: 'users',
        IndexName: "OtpIndex",
        KeyConditionExpression: "phone = :phone and otp = :otp",
        ExpressionAttributeValues: {
            ':phone': 919600923917,
            ':otp': 2387
        },
      };

Please find more details on querying global secondary indexes Doc