Help

Beautify JSON , Need help

Solved
Jump to Solution
1788 10
cancel
Showing results for 
Search instead for 
Did you mean: 
AirtableNoob
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello friends , iam a noob airtable user , i’m in charge of a table that recieves data from an api in json format , the output is all in one line , so when i try to extract a value from a key using regex it does not work (it shows error) unless i use a website or manually fix the indentations and beautify the json text .
is there anyway to fix this issue using formulas or some extension that iam not aware of ?
Thank you

1 Solution

Accepted Solutions
AirtableNoob
5 - Automation Enthusiast
5 - Automation Enthusiast

I went the scripting way at the end and i crafted a small script that go through all the records and of a specific table’s cell and just parse the json data and get the value of the key i specified and problem solved .
Thank you again for all the help guys .

See Solution in Thread

10 Replies 10

There are 2 different extensions that let you view & edit JSON text within Airtable, although I’m not sure if either of them offers a beautify function.

@Hannah_Wiginton would know more about On2Air: Amplify’s JSON functions.

There are also many websites where you can manually copy and paste your JSON to beautify it. Here’s one that I just found:

Alternatively, instead of using REGEX to parse your JSON, you could automatically parse the JSON by using the Parse JSON tool of Make.com:

Thank you for the reply , but i’ve looked into these tools , they do not provide what im looking for .
To explain a bit more what i mean .
This is what i have : (dummy data)

{ “id”: 1,“title”: “iPhone 9”,“description”: “An apple mobile which is nothing like apple”,“price”: 549,“,discountPercentage”: 12.96,“rating”: 4.69,“stock”: 94,“brand”:{“category”: “smartphones”,“thumbnail”: “https://dummyjson.com/image/i/products/1/thumbnail.jpg"},“utc”:"2022-06-23T15:37:33.0172424+00:00”, “timezoneOffsetInMinutes”:0,“timezoneIANA”:“UTC”}

and this is what i want to be done automatically using formulas if possible :

{
“id”: 1,
“title”: “iPhone 9”,
“description”: “An apple mobile which is nothing like apple”,
“price”: 549,
“discountPercentage”: 12.96,
“rating”: 4.69,
“stock”: 94,
“brand”:{
“category”: “smartphones”,
“thumbnail”: “https://dummyjson.com/image/i/products/1/thumbnail.jpg"},“utc”:"2022-06-23T15:37:33.0172424+00:00”,
“timezoneOffsetInMinutes”:0,
“timezoneIANA”:“UTC”,
}

I don’t know REGEX, but you could create a formula that substitutes every instance of “a comma and a space” with “a comma and a carriage return” like this:

SUBSTITUTE(
{Your Text Field}, ", ", ",\n"
)

That should get you part of the way there!

Note that Airtable will require you to click on the blue arrows to expand your formula field, in order to see the full results of your formula.

You can also nest multiple SUBSTITUTE statements, so you could add a carriage return after each opening bracket, too:

SUBSTITUTE(
SUBSTITUTE({Your Text Field}, ", ", ",\n"),
"{", "{\n"
)

Welcome to the Airtable community!

It could be that you need better REGEX expression. Simply adding line breaks with a formula would make the expression easier, but since you would need a pattern to know where to put the line breaks, you should be able to put that same pattern in the REGEX expression.

You can also try using scripting to parse the JSON and put the values in the proper places.

Not likely possible with a formula. You need a little script, and you need to stringify the JSON payload with two additional parameters.

JSON.stringify(myJSON, null, 2)

Since the payload starts as a JSON string, it needs to be parsed before stringifying it.


JSON.stringify(JSON.parse(myJSON), null, 2)

Further, if a script is involved, the script might as well take care of the next step as well of extracting the values.

I did manage something with REGEX but ONLY after i get through a beautifier online :
REGEX_EXTRACT({FieldNAme},‘“MyKey”[:]\s(“.*”)’)
this gets me the desired output .

@ScottWorld Thank you i figured it out but didn’t know i can nest them lol ! great tip !

Tried formatting the best i could and came up with :
SUBSTITUTE(rawText&“”,“,”,“,\n”)
but even so the problem persists , apparently the JSON needs to be perfectly formatted for some reason(?)

@Bill.French , it seems like scripting is my next route , hopefully i can manage something with my little know-how !

Thank you guys for the help and tips .

Indeed, this I assumed.

Yep - and I predict there’s no amount of regex and formula-base parsing that will ever work reliably. You must use script if you want to solve this problem.

AirtableNoob
5 - Automation Enthusiast
5 - Automation Enthusiast

I went the scripting way at the end and i crafted a small script that go through all the records and of a specific table’s cell and just parse the json data and get the value of the key i specified and problem solved .
Thank you again for all the help guys .