Cloud Firestore Case Insensitive Sorting Using Query

Cloud Firestore Case Insensitive Sorting Using Query

Sorting and filtering in Cloud Firestore are case sensitive. There is no flag to make the sorting or filtering ignore the case.

The only way to achieve your use-case is to store the field twice.

Let's say your field that stores 'AAA' & 'aaa' is called myData. In your client code you'll need to store a second field called myData_insensitive where you store a case-insensitive copy of the data.

DocA:
-> myData = 'AAA'
-> myData_insensitive = 'AAA'

DocB:
-> myData = 'aaa'
-> myData_insensitive = 'AAA'

DocC:
-> myData = 'BBB'
-> myData_insensitive = 'BBB'

DocD:
-> myData = 'bbb'
-> myData_insensitive = 'BBB'

Now you can query and/or order by myData_insensitive, but display myData.

Two interesting thing about this area is:

1. With Unicode, removing case is more complex than just 'toLowerCase'
2. Different human languages will sort the same characters differently

Without creating separate indexes for each collation to solve (2), one implementation approach to deal with (1) is via case folding. If you want to only support modern browser versions, then the following gives you a JavaScript example:

caseFoldNormalize = function (s){
  return s.normalize('NFKC').toLowerCase().toUpperCase().toLowerCase()
};
caseFoldDoc = function(doc, field_options) {
  // Case fold desired document fields
  if (field_options != null) {
    for (var field in field_options) {
      if (field_options.hasOwnProperty(field)) {
        switch(field_options[field]) {
          case 'case_fold':
            if (doc.hasOwnProperty(field) && Object.prototype.toString.call(doc[field]) === "[object String]") {
              doc[field.concat("_insensitive")] = caseFoldNormalize(doc[field])
            }
            break;
        }
      }
    }
  }
  return doc;
}

var raw_document = {
  name: "Los Angeles",
  state: "CA",
  country: "USA",
  structure: 'Waſſerſchloß',
  message: 'quıt quit' // Notice the different i's
};

var field_options = {
  name: 'case_fold',
  country: 'case_fold',
  structure: 'case_fold',
  message: 'case_fold'
}

var firestore_document = caseFoldDoc(raw_document, field_options);

db.collection("cities").doc("LA").set(firestore_document).then(function() {
  console.log("Document successfully written!");
}).catch(function(error) {
  console.error("Error writing document: ", error);
});

This will give you a document in Cloud Firestore with the following fields:

{ 
 "name": "Los Angeles", 
 "state": "CA", 
 "country": "USA", 
 "structure": "Waſſerſchloß", 
 "message": "quıt quit", 
 "name_casefold": "los angeles", 
 "country_casefold": "usa", 
 "structure_casefold": "wasserschloss", 
 "message_casefold": "quit quit"
}

To handle older browser, you can see one solution in How do I make toLowerCase() and toUpperCase() consistent across browsers

Case insensitive query on Firestore

I have created a property on db like so lowerCaseName and after that changed my query as shown below. I have inspired about this solution by this MongoDB answer.

  getSpecificTempBudgetGroup(name: string): AngularFirestoreCollection<BudgetGroup> {
    return this.fireStore.collection<BudgetGroup>(`members/${this.authenticationProvider.member.id}/budgetGroups`, ref => ref
      .where('lowerCaseName', '==', name.toLowerCase())
    );
  }

Is there a way to make a Firebase search query case insensitive without using child?

All queries in Firestore are case-sensitive.

If you need a case-insensitive mechanism you'll need to write a separate field that contains the case-insensitive version of the field and query against it. For instance:

db.collection("items").where("itemModel", "==", "Checkboard")
db.collection("items").where("lowercaseItemModel", "==", "checkboard")

There is a great answer from @DanMcGrath that I recommend you read:

• Cloud Firestore Case Insensitive Sorting Using Query

Or an alternative from @samthecodingman:

• Is there a way to search In Firebase firestore without saving another field in lowercase for case-insensitive search?

Are Cloud Firestore queries still case sensitive?

Yes, queries are still case sensitive.

To support case-insensitive or any other canonicalization you'll need to write a separate field that contains the canonicalized version and query against that. For example

db.collection("users").where("name", "==", "Dan")
db.collection("users").where("name_lowercase", "==", "dan")

Also see my longer answer here: Cloud Firestore Case Insensitive Sorting Using Query

Making a firebase query search NOT case sensitive

When using Firebase Database, that is the much you get to from orderByChild() function. It returns data either ascending or descending order and you cannot customize your result with extended queries.

However, You can try a something else that may be a bit more expensive.
Get all the children you want as an array, saving Child2 and its key. You can then change the string to upper case or lower case and access the desired result using the key.

The reference result would be

FirebaseDatabase.getInstance().getReference("Child1").child(key);

EDIT

To add on your logic, If you want "Dave" or "dave" to return Dave and dave
You can edit your query to startAt(text.toUppercase) and endAt(text.toLowerCase+ "\uf8ff"). This will return DAVE, Dave, dave etc

---

Related Topics