In MongoDB (which this is supposed to emulate) you would do something like this:
{ unique: true, name: 'name', collation: { locale: 'en', strength: 2 } }
But in AWS DocumentDB I get Field 'collation' is currently not supported
I'm hoping there is a way to do this without doing something silly like adding an extra property which is the lower cased version of the field.
I wrote to AWS Support. The only suggestion which they have provided is to use regex search query.
Regex search requires to scan collection instead of using an index, which practically is not a feasible solution at all.
Example of regex search query (use only in case you have a small database):
Inserted only one document as below:
=================================
query: db.stuff.save({"foo":"bar"});
result: WriteResult({ "nInserted" : 1 })
Performed the case insensitive search using regex:
==========================================
query: db.stuff.find( { foo: /^bar$/i } );
result: { "_id" : ObjectId("5f6848669a547e3b679499e2"), "foo" : "bar" }
query: db.stuff.find( { foo: /^BAR$/i } );
result:{ "_id" : ObjectId("5f6848669a547e3b679499e2"), "foo" : "bar" }
Inserted stemmed dcouments of the previous filed value:
================================================
query: db.stuff.save({"foo":"barxyz"});
result: WriteResult({ "nInserted" : 1 })
The search query with regex still works well:
========================================
query: db.stuff.find( { foo: /^BAR$/i } );
result: { "_id" : ObjectId("5f6848669a547e3b679499e2"), "foo" : "bar" }
query: db.stuff.find( { foo: /^bar$/i } );
result: { "_id" : ObjectId("5f6848669a547e3b679499e2"), "foo" : "bar" }
They couldn't tell me when collation or case insensitive index feature will be supported:
I cannot provide ETA in this regard as support of any new feature requires regressive testing by the development and has to go through various stages of software development lifecycle.
Linking MongoDB answer: MongoDB: Is it possible to make a case-insensitive query?
Amazon DocumentDB now supports the ability for $regex operators to utilize an index. To utilize an index with the $regex operator, you must use the hint() command and specify the name of the field you are applying the $regex on.
For example, if you have an index named product_1 on field product, you can perform a $regex search on the following documents as follows:
db.foo.insert({"product": "a product"})
db.foo.insert({"product": "another product"})
db.foo.createIndex({"product":1})
Use hint on field product:
db.foo.find({"product":{$regex:/^A pro/i}}).hint({"product":1})
db.foo.find({"product":{$regex:/^ANOT/i}}).hint({"product":1})
See https://docs.aws.amazon.com/documentdb/latest/developerguide/functional-differences.html#functional-differences.regex-indexing for more details.
DocumentDB is not mongodb and even if it works for most things you'll realize that you'll run into this type of problems the more granular you get with your queries.
Go here for the support APIs AWS Docs
Good luck.