Query DynamoDB with case-insensitive condition

27
votes

We're storing organization names in a DynamoDB table on AWS, and would like to maintain official capitalization in those business names, for example in "TNT" and "FedEx".

Our use case is that users of the application can search for organizations by name, but we'd like that their queries are interpreted case-insensitively. So, queries for "FedEx", "Fedex" or "fedex" should all return the correct item in the table.

Other databases have ways to perform queries ignoring case (for example by the ILIKE key word in PostgreSQL), by expressing queries via regular expressions, or by applying functions in the condition (for example the LOWER() function).

How can this be done in DynamoDB? The documentation on Amazon DynamoDB's Query does not provide an answer.

(The best work-around seems to be storing the name twice: once with the official capitalization in effect, and once in another field with the name converted to lowercase. Searching should then be done on the latter field, with the query search term also converted to lowercase. Yes, I know it adds redundancy to the table. It's a work-around, not an optimal solution.)

2
amazon-web-servicesamazon-dynamodb
Another possible solution (workaround) might be to create a secondary index with all the keys cast to lowercase. That secondary index can be queried or scanned, as described at docs.aws.amazon.com/amazondynamodb/latest/developerguide/…. – Jochem Schulenklopper
Duplicate questions: stackoverflow.com/questions/34921224/… and stackoverflow.com/questions/19657815/… – Sarah Messer
@JochemSchulenklopper do you have any information on how to manipulate an index value, such as casting to lowercase, when creating a secondary index? – Pappa
@Pappa, we're adding a search field to the table with locationSearch: locationStr.toLowerCase() in addition to the regular location: locationStr field. Then, when querying, we do ScanFilter: { "locationSearch": { ComparisonOperator: "CONTAINS", AttributeValueList: [locationString.toLowerCase()] } } – Jochem Schulenklopper

2 Answers

31
votes

yes, exactly, when you add the new item/row, add also a new field searchName, that is the lowercase (even more, maybe only letters/numbers/spaces) of the your name field. and then search by that searchName field

-3
votes

Writing duplicate data in dynamodb is not a good design. The best solution would be to add ' elastic search ' to dynamodb. You can connect this component ' out of the box' using the aws console. Then use custom anayzer in elastic search to get case insensitive data.