Hi @vishalpat13, as @armingrudd has identified, KNIME doesn’t like certain characters in the JSON property names. Unfortunately I was unable to find any information about what is and isn’t supported, but it’s normally safe to assume that keeping property names to such things as alphanumeric, spaces and underscores is a safer bet, assuming you have any ability to control the incoming JSON file.
Looking for any official JSON specification, I have struggled to find anything stating that single quotes are valid or invalid in JSON property names. Generally it is simply a Unicode string enclosed in double-quotes and putting your JSON through an online validator such as
jsonlint.com has said it is valid.
I agree with you that the returned error message is not particularly useful. I also cannot determine what “index 16” is referring to.
I’m really guessing here but possibly it is “close” to the position of the property in the JSON, if we ignore “some”(!) nested objects and start at 1 rather than 0, but I don’t think that is going to assist with debugging in future,
The above picture is me “clutching at straws” to find some kind of answer, so don’t assume I have any idea that I know what I’m talking about here!!
And so being unable to debug this easily is potentially an issue, because whilst a specific column-expression fix will resolve it this time, it isn’t going to assist with finding another failing property name in future . and you can’t just apply a column expression across the entire String as you don’t want to inadvertently change property values.
So, really this needs a solution that can actually fix property names should this happen again. So my challenge for this morning was to build a component to do this.
Please treat as experimental (which means satisfy yourself that it is working correctly and not introducing other errors) because I cannot test that it works for all future JSON, but I believe it should assist. It wasn’t a trivial exercise to limit the “fix” to just property names, but I believe it succeeds in this aim.
There’s always a chance that there is an easier way to do it than the solution contained inside this component. Perhaps that’s a challenge for somebody who is having an otherwise lazy weekend
Pass the component your JSON column, and tell it the characters that are considered invalid in property names. These are specified as a regex string. The default is
[',] which means remove commas and single quotes. The square brackets must be there for the regex to work. If future characters cause problems, these can be added in the config.
You could change it to the regex expression [^A-Za-z0-9_] which would mean remove any characters except alphanumeric or underscores.
You can grab the component from here:
Drop it in between your JSON Reader (I presume you are on an earlier KNIME version than I am, as I see your JSON Reader as deprecated) and the JSON Path nodes as shown here
I’d be interested to know how you get on with this.