JSON to String Node

[mwiegand]

Hi,

when saving JSON to temporary tables I once encountered an issue which required me to convert it into string. When saving JSON to rather simple file types like CSV, it is too required to get converted to string type as well.

There is a JSON to Table and Table to JSON but no such equivalent for JSON to String (but String to JSON).

It would be nice, thought, to have a node to convert JSON to string.

Best
Mike

[ipazin]

Hello @mwiegand,

there is String Manipulation node and string() function you can use. Not sure but maybe that’s the reason why there is no JSON to String node.

Br,
Ivan

[ArjenEX]

Had the same idea as @ipazin. You can also use column rename and change it to a string which is compatible to be converted back to JSON later on.

[mwiegand]

Thanks for your feedback. I too have at least one alternative solution, simply search and replace the whole string via String Replace. However, I was more thinking about making life easier for beginners especially because there is an equivalant node for table conversions as described initailly.

I also noticed there is a XML to JSON but no JSON to XML node which is a more complex scenario compared to string conversion.

Best
Mike

[ipazin]

@ArjenEX it’s not advisable to change column type with Column Rename. At least back in 2019

KNIME nodes corrupting on multiple computers

My main KNIME workflow is installed on the laptops of 15 different people. When users came to use the workflow on the 7th January, the workflow did not function as expected as the parameters of some nodes had been changed (on every users individual version of the workflow). The issue was also experienced in a previous version of the workflow that had been stored outside of KNIME since mid-December. This change was only experienced in 1 of my 5 workflows - even though the same Meta-node is incl…

@mwiegand I see you point but if node wouldn’t offer any additional functionality then I don’t see a strong reason for this node. It was never KNIME way to build a new node to offer same output you can achieve with multiple other nodes. Only way is if this functionality is used very, very, very frequently and don’t think this is the case here.

Br,
Ivan

[mwiegand]

I agree with you @ipazin, JSON to String conversion can be accomplished via other means and I can, upon second and third thought not identify a feasible feature which would justify creating a dedicated node.

Maybe, because if the presence of the presence of JSON to XML and XML to JSON I subconsciously expected the presence of a JSON to String node.

In regards to K-AI –I haven‘t tested that, though – wouldn‘t it be a little nice feature to suggest the possible solution (kind of providing a shortcut / recommendation). Especially because column type change via Column Rename Node is … not preferable.

K-Ai could utilize a curated set of recommendations / best practices based on community feedback like here?

Best
Mike

[XiozTzu]

I think there should be a JSON to String node. Reason, the JSON tools add square brackets to the output. I’m sure that is the proper JSON way, but I have to conform my JSON to what a target APIs require. As it happens the API I need to interact with does not know what to do with square brackets. So having the ability to tweak the JSON using the String Manipulator and then converting back to JSON would be very helpful. Otherwise, I would have had to write Python code to do it.

Luckly there is a solution, use the new String Cleaner on the JSON to remove the square brackets then convert the result back to JSON column with the String to JSON tool. But this is a bludgeon (hack) not a fine tool (proper way to do it).

[takbb]

Hi @XiozTzu , the square brackets in json signify that what follows is an array. If you were to remove them throughout you could end up with invalid JSON if there were any arrays of more than one element.

Are you just referring to square brackets at the start and end? These are created by some nodes (e.g. Table to JSON) as it makes sense - the possibility of there being multiple rows would by definition mean it must be any array. Whereas others (e.g. Columns to JSON)do not, because in this case it is creating one piece of JSON per row, and is not therefore an array:

Certainly Sring Manipulation or similar can be used for a “quick and dirty” workaround in some cases, and I have done it myself in the past under time pressure, rather than re-coding part of the workflow, but if you have a specific use case where you are unable to generate valid JSON in the format that you require using the JSON nodes, feel free to create a new question under the KNIME Analytics Platform category, along with a simple example, and we can try to assist with finding a better solution, (or at least better understand your specific problem)

[XiozTzu]

@takbb I understand that the brackets are arrays, but I have to form JSON that meets a 3rd parties API specs.

Left side is what I get from KNIME, right side is what is expected from the 3rd party API. If I don’t form the JSON exactly as depicted on the right, the API will not accept the JSON.

Both sides are valid JSON BTW, at least according to the JSON validators on the web.

I had to remove all brackets then add back one bracket set, because even this is handled differently than what Knime puts out. I see no way to build this JSON structure using the JSON tools I also don’t have endless time to get this out the door. Luckily, I happened upon a hack that accomplished the same thing.

But that is all just my problem. The topic here is creating a JSON to String node. Regardless of “the Knime way”, any function/node that serializes a piece of data should have a complimentary way to deserialize it. This would avoid the need to use hacks or revert to an external programming language.

[takbb]

Hi @XiozTzu , unless I am misunderstanding, turning the JSON back into a String is simply a String Manipulation call, (unless you are wanting it to do something else with the result?)
string($JSON$)

And I fully understand that time pressures mean there isn’t endless chance to try things out. Been there on many occassions!..

But whether it outputs as an array or not is down to the nodes you choose to use, so hopefully this will help for future because here I can show KNIME generating the non-array version from very simplified sample data:

Json Example - To Array or not to Array.knwf (27.1 KB)

I hope that helps!

[XiozTzu]

String Manipulation node can’t edit a JSON object only string objects.

Also, I’m jealous you got that formated so quickly. I still have the issue of getting the brackets around the operation part but I can try to do that.

[takbb]

@XiozTzu , I could only do it quickly because I’ve been exactly where you are, and have thought exactly the same thing!

The main point is the realisation (which took me a while when I first used them) that the capabilities and differences of Columns to JSON and Table to JSON are not obvious from their names. They do very similar jobs but essentially one generates arrays and the other one doesn’t. Sure there are other differences but that to me is the BIG difference.

In terms of editing… Yes String Manipulation can edit strings (bit it can handle some other data types too) but a json object is just a string that confirms to a specific format. So it can easily be turned into a string, and provided that at the end of editing it’s still in a valid format, it can be turned back to json again with String to JSON. The JSON Transformer node may be more appropriate though in many circumstances but it takes some getting used to!

[XiozTzu]

CORRECTION - String Manipulation node can edit a JSON object if you convert your JSON object to string using the string() function first.