Firestore database query, ignore case (case insenstive) and like clause.

Question

Basically I am writing a search function on a Firestore document field. I want to write equivalent of below SQL. I am not finding solution to proceed.

SELECT * FROM employee WHERE lower(employee_name) LIKE '%johny%';

Solution 1

There is no like operator. See all available operators at: https://firebase.google.com/docs/firestore/query-data/queries The page also contains different ways on querying the database.

For case insensitive sorting / querying please see the answer at Cloud Firestore Case Insensitive Sorting Using Query

It basically advises to store your lowercase data in an additional field.

Solution 2

As Norman already wrote, there is no LIKE operator in Firebase.

I wrote an answer in SO that explains how to achieve a similar query (even if it is a StartWith search, rather than a full text search):

databaseReference.orderByChild('_searchLastName')
             .startAt(queryText)
             .endAt(queryText+"\uf8ff")
             .once("value")

If you need more advanced ways of searching, then the advice is to use ElasticSearch, as a possible solution.

Solution 3

Probably answered this under the wrong topic on the other post, but check out my answer here

The solution works for me so far, but I split the name into first and last name. Then "starts with" (LIKE search%) logic should suffice since IMO people normally start typing the beginning of the names rather than something that would require a "contains" match (LIKE %search%).

Just in case the other post gets deleted due to wrong section (if that's possible) here's the snippet from firestore client perspective:

from firebase_admin import firestore 
users = db.collection("users")\
        .order_by("last_name")\
        .where("last_name", ">=", last_name.upper())\
        .where("last_name", "<=", last_name.lower() + "\uf8ff")\
        .stream()

Edit 09-03-2020 This works a portion of the time it seems. Most of the time I didn't seem to have an issue, but when I applied it to another project I was getting unexpected results. Long story short you may need to replicate how you save the data you're comparing against. For example, you may need to have a field to save the last_name in all caps and another field to save it in all lowercase, then you change the first where clause to compare last_name_upper and the second to compare last_name_lowercase. In my second project so far this seems to yield more accurate results so you may want to give that a try if the previous answer doesn't work well

EDIT 09-07-2020 Previous edit from 09-03-2020 is partially accurate. During my haste of thinking I had it fully resolved I completely forgot firebase doesn't let you use <, >, <=, >= across different fields. You may need to do two queries and merge them, but you'd probably still be reading more docs than you really intend. Doing the comparison against either the upper or lower version with the appropriate search term seems to give the original results expected from the original answer. For example:

.orderBy("last_name_upper")
    .where("last_name_upper", ">=", this.searchForm.text.toUpperCase())
    .where("last_name_upper", "<=", this.searchForm.text.toUpperCase() + "\uf8ff")

Solution 4

Cloud Firestore doesn't support native indexing or search for text fields in documents. Additionally, downloading an entire collection to search for fields client-side isn't practical. To enable full text search of your Cloud Firestore data, use a third-party search service like Algolia.

https://firebase.google.com/docs/firestore/solutions/search

View more solutions

Comments

[Vijay Keshri]

I understand that I have to store a column (employee_name_lowercase) , But how I am going to manage %john% text? I think somewhere I have to use startAt . But I am unable to complete exact result that I am looking for .

[deepelement]

This is the correct, though probably unpopular, answer.