Subselect or a subquery is a query nested inside another. A query using a subselect can look like this:
SELECT id, name, price FROM
(SELECT * FROM products
WHERE category != 'Services')
WHERE price < productionCost * 0.8
Besides readability, there are couple of reasons why we would like to structure a query like this.
Multiple inequality operators
Firebase doesn’t allow multiple inequality operators or combination of a inequality operator and ordering unless you have built an index. As building indexes is often unpractical and time consuming for ad hoc analyses, it’s convenient to perform one inequality comparison in the database engine itself and the other comparison in-memory.
Advanced conditions
Firestore only allows queries where you compare a document field to one static value. In our example, we need to compare the value in the field price to the value in the field productionCost multiplied by 0.8. Comparing two fields to each other as well as using expressions and function calls is not possible in Firestore. Jetspike allows to perform these comparisons in-memory.
On-backend vs. in-memory filtering
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. 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.
The following example shows how Jetspike can be tuned to optimize complex queries:
SELECT * FROM
(SELECT * FROM users
WHERE country != 'USA')
WHERE age > 18
SELECT * FROM
(SELECT * FROM users
WHERE age > 18)
WHERE country != 'USA'
Notice that pre-filtering by country on-backend returns 4987 documents that need to be transferred to the to your application and the filtered in-memory. However pre-filtering by age only returns 526 documents. This way you can optimize your queries to save memory consumption, execution time and network traffic.
Emulated subselect
If you don’t want to perform any on-backend comparison or ordering, you can use the short-hand notation using the emulated prefix.
SELECT id, name, price
FROM emulated.products
WHERE price < productionCost * 0.8
is equivalent to
SELECT id, name, price
FROM (SELECT * FROM products)
WHERE price < productionCost * 0.8
Further reading
The knowledge of subselects will help you to organize and optimize your queries. There are some further topics you could like to read: