How to Coda with JSON — main
Applying basic JSON knowledge in Coda
--
In my previous blog on JSON I explained basics like why the double quotations ( “ “) are important, how you open with [ ] for Arrays and { } for objects and that in most cases we deal with (nested) objects.
This is not so difficult as it looks. Certainly for those who know a bit how to Coda, you will rapidly see similarities between what you do in your docs and what is required to deal with JSON. Yes there are differences, but they are mainly hard in your mind before you look into it. Developers are not a class of exceptional gifted beings. Most are like you and me, curious people who want to make better use of data. There is no excuse not to deal with JSON logic, on top it offers great benefits as you will see later on (and in my next blog).
I came across ParseJson() only recently when I contacted Scott to ask him about the Regex he applied in his video on images in forms. As an alternative for the Regex, I developed a logic using Slice() and Find() to isolate the image URL. Both his Regex and my solution proved to be unnecessary when you have ParseJson() in your toolkit. First I had to understand what JSON was before I could see the beauty. We start with extracting the JSON Object using _Merge() + “ ”, see below (merci Scott).
Alternatively you write _Merge().ToText(), the outcome will be the same (merci Max).
and since this was a flat object (thus without nesting) we could apply the key — value logic:
Once I recognized it was about JSON and observed the elegance, I could provide a bit of support in the community.
Nested JSON Objects
So far the JSON Object (the one starting and closing with the curly brackets — {} — ) was a flat object, there was no nesting. This changed when I tried to set up a web hook to import data living in a form builder. Creating appealing and seductive forms and thus having a good lead or sales conversion is maybe the most important reason to use an external form builder (compared to the simple forms Coda offers).
Below the rather simple form I used for testing my Paperform set up.
The input one the one hand and the JSON output on the other hand make one wonder why not using a pack (like the Paperform pack created by Scott) or a third party integrator like Zapier or Make. Third party solutions require the maintenance of an extra layer which is fine if only after creation nothing changes any more and that is rarely the case. The Paperform pack only refreshed based on an automation (refresh table) or manually, but not short after somebody leaving her details in the form. For this you need a web hook, that is almost instant and that is important for the user experience. Below what I filled out and what I got as a summery in my email. The idea is to have the same information in my Coda doc. One could argue that the combination of a web hook and a packs is maybe the best way forward. Maybe, stay with me and I show you first the elegance of the web hook.
The JSON string in your doc
When you import the data in Coda it looks like below and that is not so appealing. On purpose I added some blue to show the start and the end of the Array inside the Object named “Data”:
It would be great to have something like below in Coda, that would help a lot, but since this not (yet) there we have to do it the Coda way.
A simple ParseJSON() results in what you see in step00. When we add the name of the Object “data” we get the same info, but differently presented
For a few days during the weekend I assumed that I needed to unpack this nested JSON with the help of ParseJSON(), in a direct way, but that appeared to be a mistake in the way I did it. I tried a direct chaining logic : “data.value” bit this did not work. However it worked as Eric proposed. Eric’s solution is superior, it uses yet another language dialect called JsonPath which is a kind of regex for JSON structures. its very powerful. So via twitter I got THE final solution:
RawJSON.ParseJSON("data.*.value").BulletedList()Later on Max pointed out two more ways we can write this function with the same outcome:
RawJSON.ParseJSON("*..value").BulletedList()RawJSON.ParseJSON("$..value").BulletedList()
The .. that says get all the values regardless of level. But you have to say where it starts. $ says start at the beginning, while * says start at any of the top level items (data, and all the other top level items). In this scenario both have the same result. But the meanings are slightly different.
Before I got the hand out from Eric via Twitter , I got the part below from marvelous Max — which is no longer necessary but was a great exercise ⤵️
thisRow.RawJSON.Split('"value":"')
.FormulaMap(CurrentValue.Split('"')
.First())
.Slice(2)
.NumberedList()
And here we are, we have extracted the data we got in via as JSON. And so we can apply the web hook logic to get fresh data instantly in our docs.
As said, with the described methodology we can extract the data and we are prepared for two more challenges I explain in the next blog. How did I get the raw JSON data in my doc and second how can I distribute these values over rows. Once you see this, you have a kind of Swiss knife in your toolkit to get all sorts of data out of third party software without the need of any specific pack. The Coda Formula Language will help you out in most cases.
I hope you enjoyed this article. If you have questions feel free to reach out. Though this article is for free, my work (including advice) won’t be, but there is always room for a chat to see what can be done. Besides you find my (for free) contributions to the Coda Community and on Twitter
My name is Christiaan and I support SMB with calculations (budgets and planning) and I prefer using Coda to get the job done.
Coda comes with a set of building blocksーlike pages for infinite depth, tables that talk to each other, and buttons that take action inside or outside your docーso anyone can make a doc as powerful as an app (source).
Not to forget: the Coda Community provides great insights for free once you add a sample doc.
