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

Abdul Moeez · Accepted Answer · 2020-12-23T09:28:49.897

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

The answer is updated and using global secondary indexes is your ultimate solution for querying Two Matching Fileds. — Abdul Moeez, Dec 23 '20 at 09:29

Ravindra Bagale · Answer 2 · 2020-12-23T11:12:13.467

You can use Two matching field. to do this you need to define 2 keys

Partition Key
Sort Key

Mark one field as Partition key and another as Sort Key

Example- Partition Key - phone Sort Key - otp.

if you have already made other attribute as key and not able to make otp/phone as key then you can create Secondary index and mark phone and otp as key into that.

you can mark different attributes (columns) as partition key or sort key other than already mentioned primary key or sort key in base table ( Main table).

also you can use --filter-expression

something like this

aws dynamodb query \
    --table-name users \
    --key-condition-expression "phone = :phone  \
    --filter-expression "#otp = :otp" \
    --expression-attribute-names '{"#otp": "otp"}' \
    --expression-attribute-values file://values.json

So it is not as easy as mongoDB :( – Felix Christo Dec 23 '20 at 08:44 — Felix Christo, Dec 23 '20 at 08:44

Query DynamoDB with Two Matching Fileds

2 Answers2