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

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”)

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”)

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.  

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”);

Using Not IN via Query Builder in the UI

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”;

Using Not Equal via Query Builder in the UI

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”.

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.