Creating JSON from columns with multiple values


I’d like to ask your advice on how to proceed.

I have a table Invoices, where I use Relations to retrieve data from table Orders. There can be multiple orders for one invoice.

I use Lookup to load the data into columns:
OrderName: (Order1) (Order2),…
Items: (213) (5),…
Price per item: (12) (19),…

I need to create “JSON” from this data to send to the accounting system:

“Items”: 213,
“Priceperitem”: 12,
“Ordername”: “Order2”,
“Items”: 5,
“Priceperitem”: 19,

Can you advise me how to proceed?

Thank you.


You can do it by creating your JSON object first in your order tab. Create a Javascript columns where you add all the values you need in your JSON object.

Then display in a column the JSON objects only from the order you need (based on the filter you use), join these JSON object (JSON Payload) in another column, and then in your invoice tab use a lookup to retrieve the JSON Payload.

I think this is what you need, if not let me know :slight_smile:

It’s not documented, but there is a Make JSON Object column that you can use here.



I saw this tool/column weeks ago reading a post and found it to be a great tool but I couldn’t find it in GDE when I tried. Is it in Staging by any chance?

No, it’s in production. It’s been there since late last year :slight_smile:
If you search for “JSON”, you should find it:

CleanShot 2023-04-11 at 21.17.14@2x

No way!!! :exploding_head:

I don’t know why I didn’t see and use it before

Thanks for the tip.

Saludos Darren!

1 Like

Thanks! I modified your procedure a bit, because Javascript is not my friend.

1 Like

Glad that it helped! :slight_smile:

@Darren_Murphy, correct me if I’m wrong, but you can’t feed arrays into the Make JSON column, correct? I mean, short of creating a joined list and adding square brackets in a template beforehand, but I think I recall double quotes getting in the way. I assume on it’s own, it can only create a flat single level JSON object?

I’m working on something for Lucas and I ended up using a javascript column instead to build JSON. Seemed to me that the Make JSON column is limited as far as multi-level and array JSON objects.

(Actually I scrapped the whole JSON model because JQ Query was murder on my CPU when parsing everything back out. Found that normal arrays and indexed single value columns were much faster for what I needed.)


yeah, correct. Flat structures only. To be honest, I don’t use it that often. If I’m worried that one of my key/value pairs could contain data that will break the JSON, then I’ll URL-Encode it.

Re: the JQ plugin. I stopped using that altogether for the exact reason that you noted. There is definitely something wrong with that plugin. I find that using vanilla JavaScript (JSON.parse()) is much faster and doesn’t melt my CPU :slight_smile:


Awesome. I know you use JSON a lot, so I wanted to make sure I wasn’t missing something. Good call on doing it all in javascript.


A little tip (probably you know about it)… use global variables to save on parsing and cascade declaring to avoid bugs :wink:

Hola de nuevo!

I haven’t used the JSON.parse() function in a JS code yet, but instead, I’ve used the JQ plugin so far with no problems (maybe if I put more load on it I’ll see the mentioned problems) but my question is:

How can anyone make queries in JS like JQ plugin is able to do?

I use this kind of query (with JQ plugin) for example to know how many times 2 electric lines were under 100V reading my historical data and I got the number 178 easily!


or I wanted to know when a device had a temperature >= 80oC and JQ plugin returned me these values…


Can I do and get the same results using JSON.parse() in a JS code? :woozy_face:


@gvalero The problem I had was with the Transform JSON plugin. 20+ rows and only 2 columns that used Transform JSON, and it was terribly slow. Every time I added a row it would take several seconds for the row to actually add to the table, and my CPU would max out. That was with pretty small JSON that I made within my tables. It wasn’t coming from an external sources, so a Fetch JSON column may actually work just fine.


Yes, what Jeff said.
I almost never use the Fetch JSON plugin, but I do a lot of work with JSON that I create and transform myself. In my typical use cases I’ll have a JSON structure that contains a handful of key/value pairs, and then dozens (and sometimes hundreds) of sets of these. So I’ll be expanding a JSON string across a handful of columns and hundreds of rows. The JQ plugin simply does not cope with this use case, and so I use JavaScript instead.

1 Like

Ah, I see…

So… this is the guilty ! :grinning:

1 Like

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