case insensitive query in dynamo DB
There are 2 possible ways I can think of
1) Solve at the application end by tweaking the schema
e.g Let say you have "Name" as hash key now whenever new users are added you add them after making their name in lower-case
John --> john
Doe --> doe
Remember to store both the value (name
as hash for searching) and (displayName
for displaying purpose)
Now before querying the database, you can convert you search to lower-case.
2) Use ElasticSearch: DyanmoDB table can be integrated with ElasticSearch which can perform different search operations on your table (refer link)
As other answers have mentioned, ElasticSearch seems to be a good option. However when I encountered a similar situation, this is the way in which I solved it.
I created a new attribute just for searching. I stored the values to be searched, in lower case, in this attribute and searched by querying using 'CONTAINS' operator.
eg. If I have an item as follows:
id: 1
username: 'fsmith'
first_name: 'Franco'
last_name: 'Smith'
Now for searching, when I store the item, I store it with a newly created attribute 'search_term' as follows:
id:1
username: 'fsmith'
first_name: 'Franco'
last_name: 'Smith'
search_term: 'franco_smith_fsmith'
Now if I have to search for a user with the name Franco Smith, I just make a query and use FilterExpression as(code in boto3 and python):
FilterExpression = Attr('search_term').contains('franco') and Attr('search_term').contains('smith')
However, this solution may not be very efficient depending on your use case or scenario since it uses querying and you will be charged corresponding to the items read according to the sort key condition.