I’d say the most effecitve route really depends on how your data is progressing through the workflow. Do you already have a json as input and you want to modify it or do you have something table based and want to create a nested structure from there? I assume the latter since I see Column and Table to JSON node’s in your screenshot. Something is slightly off though because your current input shows an opening array which is not closed of.
If would be possible to enrich your post with the workflow or an (anonymized) data set that is the input for your JSON, you are more likely to get someone to jump in and have a go at it
Hi @ArjenEX - thank you. I had seen that thread but it didn’t immediately jump out at me, however I will take a look again.
In the meantime, I’ve taken time to scrub the workflow and create a shareable version here. Nest JSON Problem.knwf (18.8 KB)
Do you already have a json as input and you want to modify it or do you have something table based and want to create a nested structure from there?
The latter. While the very very initial input is a JSON, it is a different query that I’m marrying up with a table. It’s not in this workflow as attached and quite upstream. So the intent is indeed to convert a table based structure into a nested json format ready to be consumed.
The current input is closed off with a square bracket, but I see I didn’t include that closure in the post. It does indeed close.
Thanks for the WF @wisemanleo The key point here is in that first Columns to JSON that you now have omitted in the shared workflow. As outlined in the referenced topic, a workable strategy here is to start with creating the nested object from the lowest level and then go up.
Yes! Thank you very much. I re-read your other thread, and it clicked this time that I had to start with the lowest object first. So I think we both arrived at similar results:
This is actually very close to the expected output.
My workflow ended up being:
And that was only by mistake…I initially tried the Columns to JSON > Collection Column > Columns to JSON approach, but it wasn’t working to what I was expecting. Had deleted the 2nd Columns to JSON and ran the workflow out of curiosity and got the above result.
That said - I do have an extra square bracket - more than I need, so I’m going to swap the collection column with the 2nd Columns to JSON node as per your workflow and see if that does the trick.
Now, if I may throw a wrench at this, but if I need to introduce "sublists: { as per below, how would I do this? I thought about a 3rd Columns to JSON, but sublists has to be at the same level as values:
{
“recordtype”: “noninventoryitem”,
“id”: 11039,
“values”: {
“itemid”: “PES623”,
“displayname”: “Sure-Pro Ultra, Embryo Replacement Catheter with Stylet, Soft 23cm Two Stage Embryo Transfer Catheter for Difficult Transfer”
},
“sublists”: {
“itemvendor”: [{
“vendorcode”: “vendor sku for item”,
“itemvendorprice”: 451.0,
“vendorcurrencyname”: “USD”,
“vendor”: 3780,
“subsidiary”: 25
}]
}
}
I think I understand Columns to JSON now. 3x nodes to the rescue! To the second / middle node, I just had to add the previous JSON output, but no other members.
Quick question. Not sure if this belongs to a new thread, but I was scratching my head on this over the weekend.
I need to add square brackets around “itemvendor”, so something like:
“itemvendor”: [{
“vendorcode”: “vendor sku for item”,
“itemvendorprice”: 451.0,
“vendorcurrencyname”: “USD”,
“vendor”: 3780,
“subsidiary”: 25
}]
I’m able to add square brackets around the whole thing, but unable to add also around itemvendor. My attempts were to place a String Manipulation node after the first Columns to JSON node:
And then performing the following:
join(“[”,$itemvendor$,“]”)
But this yields a weird result:
Apologies again if this question belongs in a new thread.
