Transforming, filtering, pivoting JSON table


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?



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

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:

I hope this helps :slight_smile: