Querying flexibly in Firestore with OR operator

[Google Cloud Platform]

You can now query Firestore using the OR operator. Your application may often need to compare one or more fields with two or more values and select the documents that match at least one of the values. This is an extension to the IN operator which could be used to compare multiple values with the same field. 

For example, if you're searching for information about cats and dogs, you could use the OR operator to combine the two search terms into a single query:

‘cats = black OR dogs = brown’

This would return results that include information about both black cats and brown dogs.

Firestore now supports OR queries via server and client SDKs, with streaming and offline compatibility.

Use Cases

Support for OR operator now enables you to utilize Firestore for building applications which need to filter one or more fields with two or more values using any of our existing query constructs. 

Examples 

Let’s say you are running an employee management portal on Firestore and you want the ability to filter on a number of criteria like Title, Organization, Location, Tenure etc., you can now easily utilize OR to be able to do it.

code_block

[StructValue([(u'code', u'CollectionReference collection = db.collection(\u201cemployees\u201d);\r\nQuery query = collection.where(Filter.or(\r\n Filter.equalTo(\u201clocation\u201d, \u201cGermany\u201d),\r\n Filter.equalTo(\u201ctitle\u201d, \u201cCEO\u201d),\r\n Filter.greaterThanOrEqualTo(\u201ctenure\u201d, 5)\r\n));'), (u'language', u''), (u'caption', <wagtail.wagtailcore.rich_text.RichText object at 0x3e6fe2216190>)])]

Now, say you want to query for performance reviews for a given employee that occurred within the last two years, or where the given rating was higher than 4. 

You can nest multiple ORs to accomplish such queries as follows :

code_block

[StructValue([(u'code', u'CollectionReference collection = db.collection("reviews");\r\nQuery query = collection.where(Filter.and(\r\n Filter.equalTo("employeeId", 1234),\r\n Filter.or(\r\n Filter.or(\r\n Filter.equalTo("year", 2022),\r\n Filter.equalTo("year", 2021)),\r\n Filter.greaterThanOrEqualTo("rating", 4)\r\n )\r\n));'), (u'language', u''), (u'caption', <wagtail.wagtailcore.rich_text.RichText object at 0x3e6fe2216850>)])]

Nested ORs can be useful when you need to check multiple conditions, but they can also be difficult to read and understand. It is important to use them sparingly and to only use them when necessary.

OR operator can also be used on all our existing query constructs like querying across Collection Groups, or in combination with existing operators like COUNT(). 

Let’s take an example of Collection Groups called Landmarks, you can now use OR operator to filter collections within the group

code_block

[StructValue([(u'code', u'Query query = db.collectionGroup("landmarks").where(Filter.or(\r\n Filter.equalTo("type", "museum"),\r\n Filter.equalTo(\u201cname\u201d, \u201cLincoln Memorial\u201d))\r\n);'), (u'language', u''), (u'caption', <wagtail.wagtailcore.rich_text.RichText object at 0x3e6fe2216fd0>)])]

We know how valuable COUNT() is to you, now you have the ability to combine it with OR to query the data more easily as shown below :

code_block

[StructValue([(u'code', u'Query query = db.collection("cities").where(Filter.or(\r\n Filter.equalTo("capital", true),\r\n Filter.greaterThanOrEqualTo("population", 1000000)\r\n));\r\nAggregateQuery countQuery = query.count();\r\nApiFuture<AggregateQuerySnapshot> future = countQuery.get();\r\nAggregateQuerySnapshot snapshot = future.get();\r\nlong count = snapshot.getCount();'), (u'language', u''), (u'caption', <wagtail.wagtailcore.rich_text.RichText object at 0x3e6fe2216bd0>)])]

Extended limits for IN and array contains any  

Excitement continues, use multiple IN operators and also combine up to 30 clauses using an IN or array-contains-any operator in Firestore. 

Example :

code_block

[StructValue([(u'code', u'Query query = db.collection(\u201cpaints\u201d).whereIn(\u201ccolor\u201d,\r\n Arrays.asList("red", "green", "blue", "yellow", "purple", "white",\r\n "black", "teal", "turquoise", "maroon", "brown", "azure",\r\n "orange", "cyan", "pink", "khaki", "gray", "gold",\r\n "silver", "bronze", "ivory", "copper", "aquamarine", "amber",\r\n "charcoal", "olive", "ebony", "coral", "lavender", "magenta")'), (u'language', u''), (u'caption', <wagtail.wagtailcore.rich_text.RichText object at 0x3e6fe93a0390>)])]

Note : Not_IN operator cannot be combined with the OR operator or have more than 10 clauses.

Next Steps 

Enjoy building apps more quickly and conveniently with Firestore.

Please refer to official documentation for more detailed information.