# Transform JSON with JQ

**URL:** <https://community.glideapps.com/t/transform-json-with-jq/56088>\
**Category:** Ask for Help\
**Created:** [December 27, 2022, 3:25pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088 "2022-12-27T15:25:17Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![bjgray](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/bjgray/32/50137_2.png) [@bjgray](https://community.glideapps.com/u/bjgray)\
**Post date:** [December 27, 2022, 3:25pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/1 "2022-12-27T15:25:17Z")

</div>

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.

 ![Screen Shot 2022-12-27 at 10.00.58 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/6/3/633f4290b14f2d346a192f24b529dd5cb0c3cbd2.jpeg)

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.

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [December 27, 2022, 4:07pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/2 "2022-12-27T16:07:22Z")

</div>

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:

 ![Screen Shot 2022-12-27 at 11.58.00 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/e/e/eeccca5114d194f8027d6461f6143d0e595bf35f.png)

 ![Screen Shot 2022-12-28 at 12.00.59 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/2/4/241459a0416eb26df325b6c5c466a175c013e148.png)

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

```

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [December 27, 2022, 4:19pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/3 "2022-12-27T16:19:48Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bjgray](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/bjgray/32/50137_2.png) [@bjgray](https://community.glideapps.com/u/bjgray)\
**Post date:** [December 27, 2022, 4:22pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/4 "2022-12-27T16:22:25Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![bjgray](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/bjgray/32/50137_2.png) [@bjgray](https://community.glideapps.com/u/bjgray)\
**Post date:** [December 27, 2022, 4:26pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/5 "2022-12-27T16:26:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [December 27, 2022, 4:29pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/6 "2022-12-27T16:29:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\
**Post date:** [December 27, 2022, 4:31pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/7 "2022-12-27T16:31:36Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![bjgray](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/bjgray/32/50137_2.png) [@bjgray](https://community.glideapps.com/u/bjgray)\
**Post date:** [December 27, 2022, 7:55pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/8 "2022-12-27T19:55:38Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [December 27, 2022, 8:10pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/9 "2022-12-27T20:10:48Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bjgray](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/bjgray/32/50137_2.png) [@bjgray](https://community.glideapps.com/u/bjgray)\
**Post date:** [December 27, 2022, 8:22pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/10 "2022-12-27T20:22:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [December 27, 2022, 8:24pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/11 "2022-12-27T20:24:33Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![bjgray](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/bjgray/32/50137_2.png) [@bjgray](https://community.glideapps.com/u/bjgray)\
**Post date:** [December 27, 2022, 8:34pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/12 "2022-12-27T20:34:55Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [December 27, 2022, 8:38pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/13 "2022-12-27T20:38:10Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [December 28, 2022, 12:08am UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/14 "2022-12-28T00:08:15Z")

</div>

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!!!

 ![Screen Shot 2022-12-27 at 7.03.18 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/5/e/5e4692d59aba11be551aef3aacd3de837a260a03.jpeg)

```auto
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… 😉

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [December 28, 2022, 12:42am UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/15 "2022-12-28T00:42:53Z")

</div>

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

> **[External Data Glide](https://external-data-glide.glideapp.io/dl/a400f7)**
>
> Super fast external data storage

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:**

> **[External Data Unlimited Glide](https://external-data-unlimited.glideapp.io/)**
>
> Super fast external data storage

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

Zero Updates!

 ![Screen Shot 2022-12-27 at 9.14.04 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/f/d/fd37385e979553d99c2be2f4e481eefa7f79b877.png)

---

<div class="post-metadata">

**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\
**Post date:** [December 28, 2022, 2:39am UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/16 "2022-12-28T02:39:16Z")

</div>

> [@bjgray](#):
>
> Could you show me what your template looks like in the JSON column?

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

 ![Screen Shot 2022-12-28 at 10.36.44 AM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/f/e/feabdb0db84d65fb9d647421c5ac05bd0ece82d1.png)

Did you solve your problem?

> [@bjgray](#):
>
> 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.

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

---

<div class="post-metadata">

**Author:** ![bjgray](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/bjgray/32/50137_2.png) [@bjgray](https://community.glideapps.com/u/bjgray)\
**Post date:** [December 28, 2022, 2:54am UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/17 "2022-12-28T02:54:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [December 28, 2022, 2:57am UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/18 "2022-12-28T02:57:44Z")

</div>

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

adjust [0] to 0 or 1 or 2…

---

<div class="post-metadata">

**Author:** ![gvalero](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/gvalero/32/1037_2.png) [@gvalero](https://community.glideapps.com/u/gvalero)\
**Post date:** [December 28, 2022, 8:14pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/19 "2022-12-28T20:14:05Z")

</div>

Hola @bjgray

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

 ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/c/9/c9c531093c00d02a5fffe30999a2d336b96dd73e.jpeg)

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!

---

<div class="post-metadata">

**Author:** ![bjgray](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/bjgray/32/50137_2.png) [@bjgray](https://community.glideapps.com/u/bjgray)\
**Post date:** [December 28, 2022, 8:49pm UTC](https://community.glideapps.com/t/transform-json-with-jq/56088/20 "2022-12-28T20:49:32Z")

</div>

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

[Next page](https://community.glideapps.com/t/transform-json-with-jq/56088.md?page=2)