Conditions

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

Operators

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

Array membership

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

Conditions​