Query DynamoDB with case-insensitive condition

Question

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.)

Answer 1

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