The JOIN clause is used to combine records from multiple collections based on a related field between them. Joining tables is one of the most important principles in relational databases, however NoSQL databases like Firestore come without this option. Jetspike helps you to achieve this functionality.
Using the JOIN keyword
Lets imagine we have two related collections orders and customers. The customerId field in the orders collection is related to the id field in customers collection.
To deliver the orders, we need a shipping address. Since the shipping addresses are stored with the customers and not the orders, we have to find the corresponding customer first.
SELECT o.date, o.price, c.shippingAddress
FROM orders o
JOIN customers c
ON o.customerId = c.id
We need to assign aliases to both collections, in this case o and c. In the SELECT section, we have to use the alias to specify from which collection to take the field, otherwise we get an ambiguous field reference. In the ON section, we have to specify which fields in both collections should be used as keys to match records. Note that you can only use the = operator, no dynamic expressions and functions are allowed.
After executing the query, we get following results:
Different types of JOINs
Jetspike currently supports INNER JOIN and LEFT (OUTER) JOIN. The INNER JOIN only returns records from the left collection where at least one record in the right collection was matched. The LEFT JOIN returns all records from the left collection regardless whether there was a match in the right collection or not. If not specified, the JOIN is executed as an INNER JOIN. The following example compares the two cases:
SELECT o.date, o.price, c.shippingAddress
FROM orders o
INNER JOIN customers c
ON o.customerId = c.id
SELECT o.date, o.price, c.shippingAddress
FROM orders o
LEFT JOIN customers c
ON o.customerId = c.id
Joining on document ID
The paymentType filed in the orders collection refers to the paymentTypes collection, however we don’t have a dedicated ID field there. Instead, the document ID of the entry is used for the relation.
SELECT o.price, o.date, pay.name AS paymentType
FROM orders o
LEFT JOIN paymentTypes pay
ON o.paymentType = pay._documentId
Further reading
You are now ready to create powerful reports on your databases. You can check some advanced use cases for inspiration. Here are some articles that could be interesting for you: