Cloud Firestore Case Insensitive Sorting Using Query

Question

I tried to read sorted data from Cloud Firestore using OrderBy. And Firestore returned data as Following Order:

AAA
BBB
aaa
bbb

Now, what I want is something like following:

AAA
aaa
BBB
bbb

I want this result only using OrderBy not by manual Sorting.
Is there any way to sort like this in Firestore?


Please provide me a solution for this.

Thanks in Advance.

Fusil

Answer 1

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

Grubbs

the more I use firebase the less I want to use it, how can something this simple be so unnecessary complex again – Flavin

Just unbelievable ! – Berardo

How about if we want to add support of querying or ordering for a field from an existing collection with huge amount of doc????So we need to scan through all the doc and add the lowercase or uppercase extra field per each? – Skimmer

@Flavin you get used to it. the performance is worth the pain – Anglonorman

Is this still true that you cannot order regardless of case? – Totalitarian

@DennisAshford yes, it's still – Gosney

What if I don't have access to write the data to firestore database? The data is stored from some third party form to firestore database. I just have to fetch the data and organize it. – Proserpina

Answer 2

You could also do it manually after you get your results:

docArray.sort((a, b) => {
  if (a.myData.toLowerCase() < b.myData.toLowerCase()) {
    return -1;
  }
  if (a.myData.toLowerCase() > b.myData.toLowerCase()) {
    return 1;
  }
  return 0;
});

Justness

Or even shorter: docArray.sort((a, b) => { return a.myData.localeCompare(b.myData) }) – Bozeman

This works for small collections but if you have a lot and are using paging in your queries the results should be ordered before they are returned to the client. – Teel

Answer 3

It's 2022. Firebase is awesome. Firestore is awesome. You don't need to stop using Firestore because of this limitation. Some things on the noSQL world are made on purpose just to speed up things.

What you can do in cases like this is just to create another property on the document, where you would parse the source value and lowercase it. Then you can use the parsed property to sort/order things.

Example:

interface ICompany {
  dateAdded: Date
  dateEdited: Date
  description: string
  id?: string
  logo?: string
  managers?: ICompanyManager
  name: string
  nameLowercase: string
  website?: string
}

Here if you want to sort companies by name. What you can do:

query(
  companiesCollection,
  orderBy('nameLowercase', 'asc'),
)

And when adding/editing:

const company = await addDoc(companiesCollection, {
  name: data.name,
  nameLowercase: data.name.toLowerCase(),
  description: data.description,
  website: data.website,
  dateAdded: new Date(),
  dateEdited: new Date(),
} as ICompany)

Voilà.

Gaut