Unlike traditional table-based relational databases, Firestore is basically just a collection of JSON-structured documents. This gives you more freedom in designing your database structure and Jetspike has tools to support you.
Accessing data in Lists
In this example, we have a collection “restaurants”. Every restaurant has a list of ratings. We want to select all restaurants satisfying a certain condition and and order them by their average rating.
The COUNT() function returns the length of a List. You can use the AT() function to access a value at an index. You can also use the aggregate functions SUM, AVG, MIN and MAX used for grouping records.
SELECT * FROM (
SELECT name, AVG(ratings) AS rating, AT(ratings, COUNT(ratings)-1) AS lastRating
FROM restaurants
) ORDER BY rating DESC
Acessing data in Maps
Similar to List, values can be accessed by their keys using the AT() function. The key can also be provided dynamically from an expression. You can use the DICTKEYS() and DICTVALUES() functions to obtain the keys and values as Lists.
SELECT name, AT(closingTimes, 'sa') AS closingTimesSaturday
FROM restaurants
Map serialization and deserialization
You can use the functions JSONENCODE() and JSONDECODE() to serialize and deserialize your Maps.
Writing and updating Lists and Maps
You can use Lists and Maps in your UPDATE and INSERT statements. To create a List, you can either enter it as a Tuple or with the function ARRAY(). To create a Map, you can either use the function DICT(<List of keys>,<List of values>) or use a deserialization from a JSON encoded string. The following command shows you the different options to enter Lists and Maps:
INSERT INTO restaurants (name, ratings, closingTimes) VALUES
('The Inn', (3, 2, 1, 4), MAP(('mo-th', 'fr', 'sa', 'so'), (21, 23, 23, null))),
('The Cafeteria', ARRAY(2, 3, 1), JSONDECODE('{"mo-th": 16, "fr": 14, "sa": null, "so": null}'))
Using the ARRAY function instead of a Tuple is necessary in cases when you only have one or no item.
What is a NoSQL database?
Firestore is a NoSQL database, unlike traditional table-based relational databases. It’s built in a way that enables almost unlimited scalability and high performance, although having somewhat limited features. In a traditional SQL database, data (rows, records) are stored in tables with defined columns. Firestore is a set of JSON structured documents organized in so called collections. Documents in the same collection do not have to share the same structure. You are also free to organize your documents with nested Lists (arrays) and Maps (dictionaries). In fact, the whole Firestore is basically a one huge JSON.
NoSQL databases lack the ability to JOIN tables, which is a characteristic feature of relational databases. Instead, nested Lists and Maps should be used. For example, instead of having two tables for orders and line items and relating them using IDs, you would create a collection “orders” and have a list of line items in each order document.
Jetspike can provide JOIN functionality for NoSQL databases. This can be very useful in some cases to explore the complicated relationships in your data, but can be performance and data access demanding. NoSQL is a different paradigm optimized for a different approach to designing your database structure. Ideally, Jetspike should give you the tools and knowledge to design optimized NoSQL databases with their native scalability and performance and SQL-like user experience and analytical capabilities.
Further reading
Great! You can now utilize Lists and Dicts organize your data. Here are some articles that could come handy: