MongoDB
To store data and retrieve information Streamsheets are able to connect to a MongoDB. Here you can learn how to add a MongoDB to your current Streamsheets installation and use it.
MongoDB Connection
A connection to interact with a MongoDB.
Settings
Enter your connection details here. An account can be used to bundle the "settings" details in one place. Select an account to use these in your connection.
Host: The URL to a MongoDB instance followed by a port. (or if working with MongoDB locally for Docker the container name)
Database: The database name to connect to
Username, Password: To authenticate a client at a MongoDB, it is common to use a username and password to sign in. Different users can have different access rights to a database.
Authentication mechanism: Choose “default” to try all. Or select a specific.
Replica Set: A replica set in MongoDB is a group of MongoDB processes that maintain the same data set. Replica sets provide redundancy and high availability and are the basis for all production deployments.
Logs
The Logs inform about the latest feedback of your endpoint. This can be successful or unsuccessful connection attempts. If you have trouble connecting, here is a good place to search for a reason.
MongoDB Functions
MongoDB can only be used via functions and not be connected to an Inbox directly. Use the target parameter of a function to connect to an Inbox (INBOX(SHEET1!)). Streamsheets offers Mongo-specific functions:
Choose the created connection in the functions.
MONGO.STORE()
=MONGO.STORE("Connection","Collection","Document")
You can start storing data as soon as your MongoDB Connection is set up. Click on a cell and enter MONGO.STORE. Remember the calculation order of a Streamsheet (top to bottom, left to right). This might play a role in the structure of your logic.
Connection: Choose the MongoDB connection you created.
Collection: MongoDB has different places to store data at. These are called collections. Type in the name of a collection. The collection will automatically be created if it does not already exist.
Document: The document contains your data. It is a simple cell range of key and value pairs. It is also possible to order them in a hierarchy.
With every calculation, one document will be stored in the selected collection.
MONGO.QUERY()
=MONGO.QUERY("Connection","Collection","Query","Target","ResultKeys","PageSize","Page","Sort","Timeout")
To look up data from a MongoDB collection, use the function MONGO.QUERY. It is possible to access all stored documents or a subset of them.
Connection: Choose the MongoDB connection you created.
Collection: Type in the collection you used in the MONGO.STORE function.
Query: The query filters the data and only gives back documents that are a match or are composed of the query. Define one or more key-value pairs the documents you are searching for have to contain. E.g., you have saved a lot of customer information and now need the data for all customers with the name “Julia” define a horizontal cell range containing “name” on the left and “Julia” on the right. To access all data, just leave this section empty.
Target: Define where it should display the response of the database. Choose a cell range or =INBOX(); =OUTBOX() as a target. Be careful. The cell range may be too small to display all information.
Result Keys: The query returns all the information saved in a document. If you are only interested in a subset, write each key in a cell and enter the cell/ cell range in the Result Key field.
Page Size: The Page Size defines how many results will be shown in your query response per page. As a default, page 1 will be returned.
Page: Select the page you want to get back. Example: Enter page size = 5 and page = 2. The query will return entries 6-10.
Sort: You can either sort by time using 1 and -1 or you can sort alphabetically by using a cell range with the key to sorting with and a 1 or -1. 1 = descending; -1 = ascending.
Timeout: Tells the function how long to wait for a response from the database(in ms).
MONGO.DELETE()
=MONGO.DELETE("Connection","Collection","Query","Target","Timeout")
Delete documents in a collection.
Connection: Choose the MongoDB connection you created.
Collection: Enter the collection you want to adjust entries in.
Query: It will delete all documents with the defined key value pair/s from the collection.
Target: MONGO.DELETE returns the number of objects that have been deleted. To get this information, define either a cell range of at least 2x2 or enter =INBOX()/=OUTBOX().
Timeout: Tells the function how long to wait for a response from the database(in ms).
MONGO.COUNT()
=MONGO.COUNT("Connection","Collection","Query","Target","Timeout")
If you are interested in the number of documents stored in a collection use MONGO.COUNT().
Connection: Choose the MongoDB connection you created.
Collection: Enter the collection you want to count entries in.
Query: Enter a cell range of key-value pairs. All documents with the defined key value pair/s will be counted.
Target: MONGO.COUNT returns the number of objects that have been counted. To get this information, define either a cell range of at least 1x2 or enter =INBOX()/=OUTBOX().
Timeout: Tells the function how long to wait for a response from the database(in ms).
MONGO.REPLACE()
=MONGO.REPLACE("Connection","Collection","Query","Document","Upsert")
To replace data in a collection, use the MONGO.REPLACE() function.
Connection: Choose the MongoDB connection you created.
Collection: Enter the collection you want to replace entries in.
Query: The query searches for documents within a collection to replace.
Document: Define a document to replace existing data with and enter the cell range here.
Upsert: TRUE or FALSE. Upsert decides if and when a query can´t be found, the document is added to the collection or dismissed.
Screenshot of an App storing & querying data
MONGO.AGGREGATE()
It is not only possible to store information and query them. MongoDB offers a variety of aggregation methods. In order to use these in a Streamsheet, you will need to work with the MONGO.AGGREGATE() function. Aggregation operators offer a variety of possibilities to interpret your data and help you generate an added value. Check out the official MongoDB Documentation to see all operators for yourself.
Connection: Select a Connection, which is connected to the right MongoDB
Collection: Enter the collection you want to replace entries in.
Aggregate JSON: The JSON-Range containing the JSON query.
Target: Define the target for the output
In the following example we want to analyze an existing database, where we stored the information on our customers (customerid, if they use the companies reward program and the total spent money on company products). Our goal is now to find out, if customers in the reward program have spent more money in total as other customers. To specify our search we only look for high value customers (high value customers have at least spent 1500$). To translate this need into the language of MongoDB we have to create a JSON Range. Here we can use MongoDB operators to formulate our aggregation. We first have to match our database entries to only list every entry with the parameter “Total spent money” over 4000. Moreover, we need to form two groups. One where the parameter “Reward program” is set to “TRUE” and one where it is set to “FALSE”. Finally, we are looking for the sum of the “total spent money” in each group, which we call “Total”.
After finishing the JSON, we now can define the parameters of our MONGO.AGGREGATE() function.
Screenshot of an App storing and aggregating data
This is only a simple example. There are many more possibilities to interpret your data set. See the full list of operators here.