Creating Nested JSON Objects

I’m a bit stuck trying to create another “nest” or “hierarchy” in my json object. My current and ideal states are below:

Current
[{
“recordtype” : “noninventoryitem”,
“id” : 22301,
“values” : {
“itemid” : “SKU1234”,
“displayname” : “Very Unique SKU Series M”,
“itemvendorprice” : 451.0,
“vendorcurrencyname” : “USD”,
“vendor” : 4209,
“subsidiary” : 07
}
}

Ideal
[{
“recordtype” : “noninventoryitem”,
“id” : 22301,
“values” : {
“itemid” : “SKU1234”,
“displayname” : “Very Unique SKU Series M”,
“itemvendor”: {
“itemvendorprice” : 451.0,
“vendorcurrencyname” : “USD”,
“vendor” : 4209,
“subsidiary” : 07},
}
}]

My current workflow is setup as such:

As always - any help or pointers would be appreciated. Thank you.

Hi @wisemanleo

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

A similar use case is discussed here:

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.

Let me know if the attached workflow helps?

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.

So it would be a first step to create

“itemvendor”: {
“itemvendorprice” : 451.0,
“vendorcurrencyname” : “USD”,
“vendor” : 4209,
“subsidiary” : 07},

In the next steps, you add a layer on top of it with the other values and so on.

So I took your JSON and demolished it again to be used as input.

As mentioned, create the lowest object first through:

Output:
image

With that object established, I “stack” the itemid and displayname on top of it as elements of values

Output:

And so on for the rest before picking it up where the cross joiners come into play.

If you apply such an approach, I get this as final result which seem to match what you have as expected output.

See WF:
Nested JSON Problem Wisemanleo.knwf (51.8 KB)

Hope this helps!

2 Likes

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:

image
This is actually very close to the expected output.

My workflow ended up being:
image

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.

1 Like

@ArjenEX
Yes, it did the trick:


image

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.

1 Like

Happy to hear it worked out! You can keep stacking forever with this :slight_smile:

3 Likes

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

And then performing the following:
join(“[”,$itemvendor$,“]”)

But this yields a weird result:
image

Apologies again if this question belongs in a new thread.

Hi @wisemanleo,

Since it’s concerning the same workflow I’m fine with continuing it here.

Quick and dirty: insert a Create Collection Column node after creating the itemvendor json.

Output:

image

It retains it as a json as well.

image

You shouldn’t have to do anything else.

5 Likes

That was easy. Thank you so much @ArjenEX. I think I understand these sets of nodes well now. Appreciate your help!

2 Likes

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