Transforming, filtering, pivoting JSON table

[phraktle]

Hi,

I’m new to Streamsheets and wondering what are the best practices for processing JSON data.

Assuming a structure like this:
[ { "id": "foo", "value": 1, "stuff": 0 }, { "id": "bar", "value": 2, "stuff": 0 }, ... ]

I was able to display it as a table with =READ(INBOXDATA(,,,),A5:J100,"Dictionary",,TRUE) but I would like to also 1) filter it so it exclude the field stuff 2) pivot the table so “foo” and “bar” becomes a column header with the value a row underneath. Also some pre-processing (eg converting timestamp strings to times, etc) would be great.

How do you generally go about these?

Thanks,
Viktor

[Tizian]

Hi,

Thank you for this question. We have not had yet time to explain this in detail, but a tutorial will come

There are two ways.

Either you know the amount of array elements in your json and want to calculate everything in one calculation step, then I suggest to work with the array elements as usual READ objects.
If you need, you could even use the IF-Cells to add further dependencies.

Or you choose the loop option. It is possible to loop through inbox messages. This turns one message into multiple, because the inbox will treat every loop element as its own message. Right-click on the inbox element to add the loop, or use the inbox settings.

I have made a small example:
Loop.json (34.4 KB)

For the data histroy see these tutorials:

How to save incoming messages in a history!

How to build a small history Often it is important to not only work with single real-time data points in the moment, but to build a history and work with a series of values. In this post I will show three different ways of creating short data histories. To access and work with larger series of data connecting to and using a database is usually the best approach. In the following example the Inbox receives messages with simulated data as payload. This data will be used to create two kinds of …

How to safe incoming messages in a history! #2

Create history tables in dependence of a parameter: In the post How to save incoming messages in a history! I already showed an example on how to safe incoming data in a dynamic history. This approach works fine, if the message structure of different sources is always different, or we use different Inboxes to work with different sources. But how do we deal with the same kind of message structure belonging to two or more sources? An example: [image] The inbox is getting “temperature” informa…

I hope this helps

Cheers

Tizian