Transform JSON with JQ

Has anyone successfully used the Transform JSON function?

I have a column of JSON data that is pulled from an API and I just need to parse a portion of that data into a form that’s useful for my app. The Transform JSON with JQ did exactly what I needed. . . briefly. Sometimes it will transform the data perfectly but then will disappear. But usually it just does nothing and appears to choke on the data. The fields are empty but when I scroll the table, cells will briefly show an icon that appears to indicate that it’s attempting to process.

The JSON isn’t exceptionally long or complex. Here’s an example:

[{“amount”:423,“commission_amount”:0,“description”:“Rent”,“is_channel_managed”:true,“is_commission_all”:false,“is_expense_all”:false,“is_taxable”:true,“owner_amount”:423,“owner_commission_percent”:0,“position”:0,“rate”:423,“rate_is_percent”:false,“type”:“rent”},{“amount”:70,“commission_amount”:0,“description”:“Cleaning Fee”,“is_channel_managed”:true,“is_commission_all”:false,“is_expense_all”:false,“is_taxable”:true,“owner_amount”:70,“owner_commission_percent”:0,“position”:1,“rate”:70,“rate_is_percent”:false,“surcharge_id”:102548908,“type”:“surcharge”}]

… and the JQ query seems pretty simple:

. | .description, .amount

I thought it might be choking because my table had 400 rows. But I tested it on a table with just a single row and got the same results.

Your JQ query looks a bit off. What is your expected result?

I prefer to use JavaScript to parse JSON, as I’ve found the JQ plugin can breakdown with large data structures. Assuming that you want to extract the description and amount, one row per array item, this is what I would do:

let json = JSON.parse(p1);
if (json[p2]) {
  return json[p2].description;

use the template column, I have big queries for thousands of rows… not a problem

Ahh. That looks so much better and I ultimately needed to break each piece into separate columns so this looks much cleaner. I’ll give this a try!

At first I thought I could do it with a template but would need to use wildcard characters. Is that possible?

I have to take look into @Darren_Murphy solution… looks interesting!
What I do, is clean up JSON with a template and replace “,” with ^^^ so I have columns and rows separators

The transform JSON column is VERY convenient, but as Darren mentions, it’s a touch slow. His JavaScript method involves a bit of coding, but it’s much faster. I use both depending on data size and purpose. Kudos to Darren for his guidance!

1 Like

@Darren_Murphy. I’m in way over my “no-code” skill level here and having a hard time replicating your example. Could you show me what your template looks like in the JSON column?

Also, I need to split the JSON data into columns instead of rows. In my table, each row is a booking and I’m trying to break out the charges for each booking.

what is the source of your JSON? is it google script? you gonna have a problem there, with numbers and TRUE/FALSE, being not a string… it will give you a hard time to split

It’s being pulled into AirTable from the API of my property management system. All the other booking data comes in great. It’s just this one list of charges that’s a problem.

I do not have any experience with air tables… in google sheets, I will make a response array.toString()…
do you have an option in AirTable to convert values to text?

I’ve got no good reason to use AirTable. I was originally building my project with Sheets and could easily switch back if it can solve this issue.

switch back… there is nothing more powerful than a combination of Google Sheets + Scripts + G WebApp + Glide App! (don’t use Glide Page… you will miss out on CSS)

Darren’s code will work, you just need to modify it a little, because you don’t have headers… Thanks, @Darren_Murphy !!! super fast and columns saver method… no need for templates, slicing, join columns, split columns, and single value columns… from JSON straight to split column… WOW… love it!!!

let json = JSON.parse(p1);
if (json[p2]) {
  return json[p2][0];

now I need to go back to my apps and change it LOL… that will cut off maybe another second from loading time… :wink:

1 Like

490 rows x 8 columns of data, uploaded under 1 second !!! WOW.

just checked my mobile… under 3 seconds… not bad!

I think I’m gonna make my own Glide platform, with super fast, unlimited data… LOL


Hhahahahahahahah!!! Free App… 1,500 rows… under 2 seconds… LOL
Mobile 7 seconds…
I’m having so much Fun! :rofl:

Zero Updates!

1 Like

I just pasted the sample JSON that you provided into a template column.

Did you solve your problem?

I don’t understand. Given the sample JSON that you provided, can you show me what your expected output is?

Any chance you can provide a little more detail for the slow learners in the room?

the only difference is to get columns, not headers…
return json[p2][0];

adjust [0] to 0 or 1 or 2…

Hola @bjgray

Just a curiosity, is this empty data in your records (rows) caused by ?

Are the values of your charges column static values/text already saved or are they
created dynamically every time your App is running and makes API requests?

Thanks, saludos!

Those are bookings that didn’t have any charges. Could be cancellations or test bookings, etc.

The charges column is dynamic. It pulls data from my property management system API