Basic queries

Jetspike’s powerful querying capabilities allow you to filter documents, specify column mapping and apply functions to the output

Docs

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.

Filtering

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.

Selecting columns

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:

SELECT
UPPER(name) AS productName,
price * 0.8 AS priceDiscounted,
IF(available = TRUE, 'Available', 'Out of stock') AS availability
FROM products

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.

Subselects

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.

Further reading

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: