Jetspike’s powerful querying capabilities allow you to filter documents, specify column mapping and apply functions to the output
Performing a simple query
After connecting to your Firestore project, you are ready to execute your first query. A simple query can look like this:
SELECT * FROM products
This query returns all data in all documents in the collection “products”. After clicking on execute, query results appear in the lower part of the screen.
Sometimes we only want to return documents satisfying certain conditions. The following query returns all products in the category “Services” with price over 100:
SELECT * FROM products
WHERE category = 'Services' AND price > 100
Read more about conditions, their combinations, operators and their limitations.
Using the * symbol selects all columns. Alternatively, you can specify columns to be returned manually. The _documentId column contains the ID of the document and is only returned when specified explicitly. Jetspike also supports column aliases with the keyword AS.
SELECT name AS productName, price, _documentId
FROM products WHERE category = 'Services'
Expressions and functions
You can apply operations like addition or multiplication and execute a wide variety of built-in functions. Following query shows an example:
UPPER(name) AS productName,
price * 0.8 AS priceDiscounted,
IF(available = TRUE, 'Available', 'Out of stock') AS availability
See the full Built-in functions list for further information.
Ordering and limiting
You can specify multiple columns to order by, however this can result in a Firestore error being raised if an index is not created. You can also specify the maximum number of rows to be returned.
SELECT * FROM products ORDER BY name, price DESC LIMIT 10
In the free version of Jetspike, maximum number of returned rows is set to 100. See Pricing for details on upgrading your plan.
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. All higher level selects run in the RAM. This offers more advanced functionality but can be heavy on performance. Also, if performing filtering in memory, all data have to be transfered from Firebase first.
SELECT name, price FROM
(SELECT * FROM products WHERE category = 'Services')
See here for a more detailed comparison of both methods.
Congratualtions! You now know how to query documents and organize the returned data. There is some suggested reading to further boost you data analytics superpowers with Jetspike: