how to check if non-key attribute already exists in dynamodb using ConditionExpression?

Question

I want to insert into users table only if userId, email and username does not exist ( want these to be unique).
userId is the primary key ( Hash key, data type - Number ).
username and email are non-key attributes ( both string ).

Here is how i tried:

response = userTable.put_item(
Item={
    'userId': userIdNext,
    'accType': 0,
    'username': usernameInput,
    'pwd': hashedPwd,
    'email': emailInput
},
ConditionExpression = "(attribute_not_exists(userIdNext)) AND (NOT (contains (email, :v_email))) AND (NOT (contains(username, :v_username)))",
ExpressionAttributeValues={
    ":v_email": emailInput,
    ":v_username": usernameInput
}
)

I tried to follow the aws documentation for logical operators and condition expression from here: AWS Conditional Expressions

But it is inserting everytime into table even if username or email already exists in the db.
( i am giving new userIdNext as it is primary key and cannot be a duplicate )

I am using Python implemetation boto3

Answer 1

dynamodb can force uniqueness only for hash-range table keys (and not for global secondary index keys)

in your case there are 2 options:

1) force it on application level - query for records, and find if duplicate

2) add another dynamodb table with hash/range values (that can enforce uniqeness), you can query this table before putting an item to the main table

3) use application locks (memcache..)

4) dont use dynamodb (maybe its not answer your requirements )

referring you to answers here:

DynamoDB avoid duplicate non-key attributes

DynamoDB consistent reads for Global Secondary Index

Answer 2

Ok, i found the bug in my application.

I trying to do a conditional put item to DynamoDB but I'm passing a different primary key that one that exists on DynamoDB, at this point a new object will be created and conditional expression was ignored.

# id primary key
id = 123
# email is only index
email = "[email protected]"

item = {
  "id": id,
  "email": email
  "info": {
    "gender": "male",
    "country": "Portugal"
  }
}

response = self._table.put_item(Item=item, ConditionExpression="email <> :v_email", ExpressionAttributeValues={":v_email": email})

If you want to conditional expressions work fine on DynamoDB put_item you should send the same primary key of the one that exists on DB.

kishorer747, can you put a sample of your code that do this? "i have Global Secondary Indexes to check if username or emails exists already, but its not atomic. So if 2 users query at the same time and get that email1 is available, they both insert into users table. And if there is no check to verify if email/username does not exist already, we have duplicate entries for email/username "