Advanced filtering allows you to retrieve documents based on both static and dynamic conditions
Query with a condition
The following query returns all products in the category “Services” with price over 100:
SELECT * FROM products
WHERE category = 'Services' AND price > 100
Following operators are available:
=, == equal to
!=, <> not equal to
< less than
<= less than or equal to
> greater than
>= greater than or equal to
Static vs. dynamic conditions
Jetspike supports subselects. It is important to note that only the lowest level select runs in Firestore. This is very fast but some functionality may be unavailable due to Firestore limitations. Only static conditions are allowed where the value of a field is compared to one static value. Non-equality conditions on multiple columns require an index to be created.
All higher level selects run in RAM. This offers more advanced functionality but can be heavy on performance. If performing filtering in memory, all data have to be transferred from Firebase first. This option allows for dynamic conditions, for example comparing values in two columns:
SELECT * FROM (SELECT * FROM products) WHERE price < productionCost
This is equivalent to a shorthand notation using the emulated schema:
SELECT * FROM emulated.products WHERE price < productionCost
In memory conditions also allow for using expressions and built-in functions:
SELECT * FROM emulated.products WHERE price - productionCost > 10
SELECT * FROM emulated.products WHERE COUNT(reviews) > 5
SELECT * FROM emulated.products WHERE created < GETDATE()
Use the ARRAY_CONTAINS function to test whether an array-valued column contains a certain value:
SELECT * FROM products WHERE ARRAY_CONTAINS(regions, 'Europe')
Use the ARRAY_CONTAINS_ANY function to test whether an array-valued column contains any of multiple specified values:
SELECT * FROM products WHERE ARRAY_CONTAINS_ANY(regions, ('Europe', 'North America'))
The IN operator tests whether a value is contained in a provided array:
SELECT * FROM products WHERE category IN ('Services', 'Devices')
This is equivalent to using multiple equal to in a compound condition with OR (which is currently not possible with Jetspike).
The NOT IN operator tests whether a value is not contained in a provided array:
SELECT * FROM products WHERE category NOT IN ('Services', 'Devices')
This is equivalent to using multiple not equal to in a compound condition with AND. Jetspike will automatically optimize towards using NOT IN.
Testing for NULL and non-existing field
Let’s imagine a following situation with three documents – one has String-valued ‘category’ field, the other has NULL in the ‘category’ field, in the third document, the field is not present at all.
When testing for == NULL, Firebase level query will only return documents where the field value is exactly equal to NULL. In virtual query, all documents will be returned where the field value is NULL or missing.
SELECT * FROM products WHERE category = NULL
SELECT * FROM emulated.products WHERE category = NULL
In Firebase it is not possible to test for field existence. The HAS_FIELD function can be used in a virtual query.
SELECT * FROM emulated.products WHERE HAS_FIELD('category')
Conditions in UPDATE and DELETE
Conditions can also be used in UPDATE and DELETE statements. The following example deletes all unavailable products from the database:
DELETE FROM products WHERE available = false
All UPDATE and DELETE queries can run as virtual, therefore you can use the full functionality of dynamic conditions.
UPDATE emulated.products SET price = productionCost * 1.2 WHERE price < productionCost * 1.2
You just mastered advanced conditions in Jetspike. Following topics can further deepen your knowledge: