Extract data from complex JSON with regular expressions

[Paul_Danyliuk]

This is more of a lesson in efficiency. Inspired by this post:

Reading doc type field

Hi, thanks for the link. Unfortunately Jira description is a bit more complicated and i could not work out how to parse its JSON and .filter doesn’t seem to be working. I managed to extract the json below and I also shared the document I need to collect all “text” values “type” : doc, “content” : -{ “type” : arr, “value” : -[ -{ “type” : obj, “value” : -{ “type” : paragraph, “content” : -{ “type” : arr, “value” : -[ -{ “type” : obj, “value” : -{ “text” : We need to choose a page buil…

Task: there’s a JSON with extremely nested structure. Need to extract all values for the key "text" anywhere in the JSON tree.

The first instinct would be to parse the JSON into objects and try to traverse the tree somehow. However, in this very scenario, "text" entries don’t all appear at the same level of nesting. So traversing and collecting them would require composing some complicated logic in Coda.

Efficient solution: treat JSON as plain text and simply extract pieces of data with regular expressions:

MyJSON.RegexExtract('(?<="text"\ *:\ *")(?:\\"|[^"])*', "g")

Explaining regular expressions is well beyond the scope of this topic. But knowing them is a very useful skill. There are lots of tutorials on regular expressions, and websites like regex101.com that let you compose and test regular expressions online with explanations.

What this regex above means is:

1. Read the text until you find a spot just after "text" : " (spaces are optional)
2. Now read the text until you find the closing quote " (and don’t stop at escaped quotes \").
   This will be the string we want to extract (i.e. the value after the "text" key)
3. Repeat this for the rest of the text (as instructed by the ‘Global’ g flag)

RegexExtract is an experimental hidden formula. It is handy because it returns a List of strings, which you can then process, e.g. unescape newlines and special chars, and join all strings together with newlines:

thisRow.[My JSON].RegexExtract('(?<="text"\ *:\ *")(?:\\"|[^"])*', "g").FormulaMap(
  CurrentValue.RegexReplace("\\n", Character(10)).RegexReplace("\\(.)", '$1')
).Join(Character(10))

It is possible to achieve the same logic with RegexReplace to avoid using an experimental formula. But that would be a bit trickier and less of a presentation in how to use outside-the-box approaches to solve one’s problems more efficiently.

Example (with a very basic JSON really — the above would work with a much more nested tree too):

[Bill_French]

Paul,

A very clever use of internal Coda capabilities. As I read this, it occurred to me that you can learn so much from this one hack. It reminds us all that with RegEx, we can transform almost any cul-de-sac into a thru-street. This is a nicely crafted approach that has deep educational value.

@Paul_Danyliuk

Efficient solution: treat JSON as plain text …

However, another way to describe this is potentially a Brittle Solution.

One of the most important aspects of JSON is the ability for tools and services to make changes without breaking pre-existing dependencies. I think it’s fine to create hacks where there are no other options. But ideally, a hack like this may encourage the Coda developers to consider adding a JSON parser along with the ability to retrieve (or POST) JSON data effortlessly to and from other services.

.

.

[Paul_Danyliuk]

Thanks!

Not sure what’s so brittle about this though. It will keep on working even if the tree structure changes (e.g. it becomes even more nested), whereas e.g. path-based queries would fail. So this solution may in fact be a Sturdy one

There is a JSON parser in Coda: ParseJson(json, path)

But again, it either parses the whole structure into objects and lists, or a predefined path (or a list of paths IIRC). It would be awesome if the path parameter supported queries like JSONPath — but it feels like it doesn’t:

---

But you’re right. The point of this exercise is to remind Coda makers about the power of regex, and that it often may be a good idea to explore non-trivial solutions to tricky problems that come.

[Bill_French]

@Paul_Danyliuk

There is a JSON parser in Coda:

Ha ha! Shows you what I know! I’m old - I get it wrong a lot.

I used the phrase “potentially brittle” because anything in the scraping realm tends to have risks. But everything has risks as you point out.

For example, what happens when test contains 30 items? Or when the collections array in test is expanded to include additional arrays?

Your regex may continue to work, but I’ll bet there are changes that could create some turbulence where no such impact would be felt by the use of the parser.

[Bill_French]

I was so happy to see RegEx in Coda when I first used the product. It is the Swiss Army knife for getting us out of jams. It makes me look like MacGyver, well feel like MacGyver.

[Paul_Danyliuk]

@Bill_French

what happens when test contains 30 items? Or when the collections array in test is expanded to include additional arrays?

Nothing would change; regex doesn’t care about the {}s and []s in the text. It is set up to only match literally the longest subsequence that doesn’t contain an unescaped quote, which comes right after a "text" : " sequence

Or, in other words, a value for the text entry anywhere. All of them.

But I get what you’re saying. What if we only need some of the text entries and not all of them. Then yeah, discriminating with regex would be much harder.

[Jean_Pierre_Traets]

Hi @Paul_Danyliuk, thanks again for your detailed explanations and the reference to regex101.com

The approach is similar to this post:

Hack to filter lots of data

Here’s something you might enjoy, I updated this example to make the search box personal (i.e. each user’s search won’t disrupt anyone else’s): [image]

While yours is tailored to the JSON code

Just from a curiosity point of view, I will try to find the soonest some time just to play around because as always you keep inspiring me with your creativity and search to bring things down to a more “basic” level, although even the “basic” level is not always that easy for me to get my head around.

Different age and background, but still eager to learn new dimensions

[Bill_French]

@Paul_Danyliuk

What if we only need some of the text entries and not all of them. Then yeah, discriminating with regex would be much harder.

Right. That’s kind’a separate from the potential of a breaking change that stops it from working. Once you get to a certain point, element filtering (while possible with RegEx) becomes quite tedious and JSON parsers come to the rescue.

But the idea of exploiting RegEx is the real story here - this is a very powerful feature that we need to know and it seems we never fully realize its potential to solve parsing challenges. RegEx is one of the foundations of building smart documents and solutions - it is literally at the doorstep of AI.

[Paul_Danyliuk]

@Paul_Danyliuk

It would be awesome if the path parameter supported queries like JSONPath — but it feels like it doesn’t:

Reviving this thread to say it’s not true anymore — ParseJson supports JSONPath now!
No more regexes to parse JSON.

How to extract @People and other @-references from a Text cell

A client asked me today how he could take a text field like this and extract people from it: [image] The “straightforward” ideas like using Split didn’t work. Links and @-references in formatted text are unlike the rest of the text: technically they occupy just one empty character and instead have an extended encoded descriptor telling what should be displayed in place of that empty character. Running a formatted text field through just strips those descriptors and that’s what you get: [imag…