Jump to Content
Developers & Practitioners

IN, NOT_IN and NOT EQUAL query operators for Firestore in Datastore Mode

July 8, 2022
Harsh Prajapati

Software Engineer, Firestore

We’re very pleased to announce that Firestore in Datastore mode now supports IN, Not IN, and Not Equal To operators.

IN Operator

Firestore in Datastore Mode now supports the IN operator. With IN, you can query a specific field for multiple values (up to 10). You do this by passing in a list of all the values you want to query for, and Firestore in Datastore Mode will match any entity whose field equals one of those values.


For example, if you had a database with entities of kind Orders and you wanted to find which orders had a “delivered” or “shipped” status, then you can now do something like this: 


Example:

SELECT * FROM Orders WHERE status IN ARRAY(“delivered”, “shipped”)

https://storage.googleapis.com/gweb-cloudblog-publish/images/image4_pnv9IXc.max-800x800.png

Let’s look at another example: say Orders has a field Category that contains a list of categories in which the products in the order may belong to. You can now run an IN query on the categories that you are looking for.


Example:

SELECT * FROM Orders WHERE Category IN ARRAY(“Home Decor”, “Home Improvements”)

https://storage.googleapis.com/gweb-cloudblog-publish/images/image5_YsknAoN.max-800x800.png

In this case, each entity would only be returned once in the query even though they match both the categories in the query.


You are now also able to use ORDER BY on both IN and Equal. The query planner originally ignored ordering on an equality, but with the introduction of IN, ORDER BY queries on multiple-valued properties now become valuable. Please make sure to check out the official documentation for additional details.  


You can also use the new Query Builder in the UI to use the IN operator.
https://storage.googleapis.com/gweb-cloudblog-publish/images/image3_dHyzvVi.max-1100x1100.png

Not IN & Not Equal Operators

You can now query using Not IN, which will allow you to find all entities where a field is not in a list of values. For example, entities with kind Orders where the status field is Not IN [“shipped”, “ready to ship”].


Example:

SELECT * FROM Orders WHERE status NOT IN Array(“shipped”, “ready to ship”);

https://storage.googleapis.com/gweb-cloudblog-publish/images/image2_h56WjIx.max-800x800.png

Using Not IN via Query Builder in the UI

https://storage.googleapis.com/gweb-cloudblog-publish/images/image7_RAUHk3M.max-1100x1100.png

With Not Equal you can now query for entities where a field is not equal to some given value. For example, entities of kind Orders where the status field is not equal to the value “pending”.


Example:

SELECT * FROM Orders WHERE status != “pending”;

https://storage.googleapis.com/gweb-cloudblog-publish/images/image1_8lV6oGO.max-800x800.png

Using Not Equal via Query Builder in the UI

https://storage.googleapis.com/gweb-cloudblog-publish/images/image6_abvI0yR.max-800x800.png

Note that with Datastore’s multi-value behavior using Not IN and Not Equal requires only one element to match the given predicate. For instance, Category Not IN [“Home Decor”, “Home Improvements”] would still return both e1 and e2 since they contain the category “Kitchen” and “Living Room”.

https://storage.googleapis.com/gweb-cloudblog-publish/images/image2_h56WjIx.max-800x800.png

We hope these new additions enhance your development experience. We look forward to learning about how you’ve taken advantage of these new features, thank you! Please visit the official documentation to learn more.

Posted in