Transform JSON with JQ

@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

Update:

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

Big “THANK YOU” to each of you. This was a challenge I’d been struggling with for several days. It’s working exactly as I’d wanted now.

so…what was the problem, and what solution?

The problem was how to parse only the data i needed from a JSON string and place each piece into separate columns. The Transform JSON function wasn’t working but @Darren_Murphy’s JavaScript technique along with your tweak worked perfectly once I figured out how to use the P2 field in the JavaScript column.

Ok… thanks again!

Let’s play some scenarios in order to be more efficient if the circunstancies allow it:

  1. how many rows (bookings) does your table have? Let’s assume that it has 2,000 rows.
    if your APP is used by 5 users and each one opened it 3 times in 1 hour to see the charges, did your management system API have to handle 30,000 requests in 1 hour?
    It’s too much data and work according to my point of view.

  2. Wouldn’t it be better to parse the JSON payload only when the user presses the details screen?
    I mean, the user sees the customer list from your APP, if he wants to see a specific charge list belonging to a customer, the APP will load and show the Details screen and from this point on, your app will fire an API call requesting data from that specific customer and then parse the JSON payload putting that information into multiple columns (as many as you want).

Probabilistically (I think), your API must work 1-2% only in this case and your APP should be faster.

I hope it helps, have a nice night.

Saludos!

If you’re hard coding the p2 value, then you don’t need to bother using it at all.
ie. in the example you gave, you could just write:

return json[1].amount;
1 Like

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.