Starting with streamsheets

Streamsheets Issues
1

Just making my first steps into streamsheets I tried to go through the tutorials.

As a general feedback the gif-tutorials are not an ideal solution. As they cannot be stopped or started on a certain point, I got stuck several times because I missed some details. There are a lot of preconditions that easily get lost: “Do I need a topic in a consumer and a producer or not ?!?”. The videos often jumps over this details so I needed to repeat every gif many times.

Explanations often are very short and paramters not described.

Example: mongo query explanation

  • QueryJSON: Define a query using a JSON cell range (But what format? {A1}, A1 ??)
  • ResultKeys: Optional. Filter the query result. (dito)
  • PageSize: Optional. Number of Documents to return per page (What page?!?)
  • Page: Optional. Index of page to return. (dito)
  • Timeout: Optional. Define Timeout. (Ah, but in what unit? s, ms?)

So, currently I was left to lot´s of try and errors.

Are there maybe some working examples, that can be downloaded? This would help much as a starting point.

1 Like
2

Hi Eckehard,

Thank you for this feedback!! You are absolutely right. The tutorials and documentation are in a very early stage. We will have to work on these. :construction_worker_man:

Especially the tutorials of MongoDB are not yet perfect for beginners. This is due to the fact, that MongoDB is a very powerful data base with a lot of features, which we try to integrate.

We will certainly add a better and more in depth version of a “how to” on MongoDB. Right now, I agree that most of the terms are not very helpful, if the user is not familiar with the MongoDB Syntax. :exploding_head:

I prepared an example for you, which covers some MongoDB functionalities. I hope this helps!

MongoDB Function Example.json (95.6 KB) (for v1.4) Update: Make sure the connector settings work for you. The example connects to “mymongodb”.

Until we have a better way to understand MongoDB (we have several features planned), check out the MongoDB Documentation to understand their syntax! :slight_smile:

Cheers

Tizian

3

Hi Tizian,

for any reason my screen is mixed up with a large white border, so it is hard to see the page (running on chrome). If I maximize the page, the scroll bars are not visible any more.

35%20PM
Screen Shot 10-28-19 at 09.35 PM.PNG1421×745 159 KB

I can see that your example is quite complex and really confusing. Before I had one question (how to set up a mongo time series to see the histroy of an mqtt source). Now I have hundreds…

My first steps have been much simpler. You see an image of my screen. I have an mqtt source that delivers some data every minute. I just wanted to see a history of my data, but that is what I got:

I used =MONGO.STORE(|MongoStore,“test1”,JSON(A2:B7)) to store the data.

=MONGO.COUNT(|MongoStore,“test1”,JSON(A2:B3),A12:A13,10) gives a strage result

=MONGO.QUERY(|MongoStore,“test1”,A16:F22) delivered a result, but not what I expected.

In your example it seems, that mongo is not used to generate a time series at all?!?

4

By the way: The JSON() function is not explained in the help files…

5

Hi Eckehard,

Bug: The white spaces are a known issue and will be fixed with the next release. This happens, if you zoom with your Browser instead with the Zoom Tool in the Streamsheets. Check if you can see this sign: image

MongoDB:
By trying to cover most of the MongoDB functions, I might have made the example a bit too complex. Sorry about the confusion. :confused:

Time Series:
It is possible to create a time series in MongoDB. You can sort by the creation time of the MONGO.STORE entities using either “1” or “-1” in the sort parameter of your Query. With every MONGO.STORE MongoDB automatically stores an “_id” Value to have a unique key this is used to sort items.
If you want to sort by other parameters you can add the data range of cells looking like this to the sort field,
image
{ arduino_test: 1}

or use MONGO.AGGREGATE and the MongoDB operators.

Did this answer your question?

Count:
The problem with MONGO.COUNT seems to be, that somehow there is a problem displaying a count number of 0. I will look into this. Thank you for bringing this to my attention!
Reason for the count number of 0 is, that Streamsheets usually calculate faster, then the information can be processed from the MONGO.STORE to the DB and back to the MONGO.COUNT within one step of a Streamsheet.

Since you are counting items, which are very likely to be unique key value pairs (I guess the combination of the values of “arduino_test” and “arduino_test_delta” are always unique), the answer of the count will always be 0. Here you can also use the INBOX() entry if you want.

Query:
It can be easier to use the Inbox as a target for your query response (use INBOX() instead of a cell range). This way you have an overview over all data. The grid is only showing you the amount of data possible to display within the given TargetRange.

=MONGO.QUERY(|MongoStore,“test1”,,INBOX())

This seems to be the case for your query. The reason {JSON Object} is shown in column F is because there is not enough space left to display all your data. The parameter “arduino_test_lost” and “PacketDifference” don’t fit in one column. Further, we again have the issue, that zeros are not displayed very well. We are looking into this issue.

JSON():
You were right, I have missed the JSON() function. I added it to the documentation.
It basically structures the data in cells into a JSON Object. Streamsheets converts all data that flows into JSON. But also other Systems use JSON as a data format. E.g. MongoDB uses JSON to store information.

I hope this help :slight_smile:

Cheers

Tizian

6

Hy Tizian.

indeed, your explanation helps and hopefully serves other to manage their first steps.

As a general feedback it seems very strange to me. that every sheet can have a different event that triggers the calculation. This makes results a bit unpredictable.

My data source is fairly slow, it fires every 5 minutes only. So I have to wait for the next packet to see a result. If I change the event to continuous, the database fills up with current values. Feeding the query back to inbox has strange results then.

I feel that this will bring trouble if we need to handle lot´s of asyncronous datasources. I think there sould be a more logical approach…

Referring to the new JSON() explanation: That looks really nice and is probably a perfect place to save future answers to user questions.

I immediately have some:

a) What are the rules to evaluate a range, if it is not a group of pairs? Maybe we select 3x3 cells
b) A group of key/values can be A1:B5 or A1:E2. Which can be used?
c) What about more complex JSON structures like arrays?
d) If I have a cell with {JSON Object}, how do I get the elements back? Is there any function to get a single value out of this cell?

Thank you anyway for your attention

7

Glad to help :slight_smile:

I agree, that at this point things might get a little more difficult, but this is also because the use case is not that simple anymore.
There are multiple ways to deal with asynchronous data streams depending on your goal. Here some examples:

All together: Basically always just put a dependency to the MONGO.STORE() and you wont have the issue of invalid data being stored :slight_smile:

About your other questions:

a) What are the rules to evaluate a range, if it is not a group of pairs? Maybe we select 3x3 cells

I tried to state this in the JSON() explanation, but maybe not clear enough:

A data range over at least two columns, only interpreting the first and last column into the JSON Object.

Meaning, that as soon as there are more than two columns involved, the middle columns will be ignored.
Try a MQTT.PUBLISH with a JSON(A1:C3) Range. This will discard column B data, since only a data pair is a valid input.

b) A group of key/values can be A1:B5 or A1:E2. Which can be used?

Both can be used, but the JSON Range is oriented in columns not in rows, so the pairing will only work for A1:B5.

c) What about more complex JSON structures like arrays?

I guess you are talking about hierarchies with JSON Objects? Check out this topic here :slight_smile:

d) If I have a cell with {JSON Object}, how do I get the elements back? Is there any function to get a single value out of this cell?

How did you manage to get this? I guess over a Mongo.Query?
If your Range on the Sheet is big enough this is likely to be a bug, which will be fixed for the next release (empty spaces and zeros are wrongly interpreted on the sheet at the moment).
Generally speaking there is no way to get a single value from a JSON Object, since there would be no logic to it. You can always visualize this by sending it to an Inbox. Use either MQTT.PUBLISH or even try out FEEDINBOX() (see also example in the JSON() documentation).

Cheers

Tizian

8

Hello Tizian,

thank you much for the explanation. Part of this is surely good to find in the help, as it is not self explaining.

Referring to d) maybe you misunderstood me? If I put in a cell =JSON(A2:B7) the result is {JSON Object}. But how do I access this opject?? Usually you can reference any part of a JSON object.

So I would expect, that there is a function to work with the object like =query(A2,“key”) that gives a certain value of key.

1 Like
9

Hey Eckehard,

Thank you for your feedback!!
Yes, we will continue to add more and more tutorials! :slight_smile:

d)
You are right, this would make sense, if your goal is to store a lot of data within a Streamsheet.
We are actually planning a table feature, which goes in the direction of what you want to do.
For now, we try to not encourage user to store too much data within a sheet. The reason for this is, that more data in a sheet the bigger the impact on the performance. This is also the reason why we limited the amount of rows and columns.
For smaller data sets, the amount of cells should work for now, anything beyond this has to be solved over a database.

For the future, we are planning some kind of “table functions”, which will kind of work like your suggestion, but also solve the performance issues. :construction_worker_man:

Cheers

Tizian