having issue with converting json fields into a table

Hello Folks,
I need help with converting a JSON file into a table format. This is important because I want to work with the table data for other tasks later on. While I have some basic knowledge of Knime, I’m not an expert.

In the attached workflow, I am getting some error as: Property must be separated by comma or end with close square bracket
Micrometric event process workflow.knwf (20.4 KB)
Micrometric Event Tagging Requ All Items.json (28.7 KB)

I would really appreciate it if you could spare some time to assist me. Thank you!

Hi @vishalpat13 ,

Knime have some nodes to convert Json as string, table and make path. If the Json have multilines, I suggest to use the path because you can map the first block and set multilines, converting as result into a table with columns that you named and the values (string, number, array, another json node…)

About your error, maybe something is missing into the original json as said “Property must be separated by comma or end with close square bracket”. All start and end nodes are correctly closed?

Tks,

Denis

1 Like

Thank you for your time and efforts @denisfi .

I understand what you are suggesting but if you can go through the workflow that I have provided and then give me any solution, that may work

also, this is my full error description: JSON to Table : 0:2 : Execute failed: Property must be separated by comma or Property must be terminated close square bracket at index 16

so, what is index 16 here ?

I have already tried available json nodes and also validated json file

Thank you

@mlauber71, @takbb Please check this issue and help me out in simple terms

I have provided workflow and json file along with the post

Hi @vishalpat13,

Apparently, the single quotation mark in the object names is not supported. Therefore, the object “Select client’s E-commerce platform” is the cause of this issue. You can simply fix this by using the KNIME Expressions node and modifying the object name in the json.

Example expression to use:

replace(column("json"), "Select client's E-commerce platform", "Select clients E-commerce platform")

You can save the changes directly to the json value by checking the “Replace Column” option and selecting the json column in the configuration dialog of the node:

You can edit the column name later in the workflow to the same name as in the json.

3 Likes

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

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

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

image

I’d be interested to know how you get on with this.

2 Likes

Updated the component to return a second output port which is the list of property names with any changes, so you can use this to rename them back again afterwards if you need to.

1 Like

Hi @takbb , Thank you for giving prompt replies to each issue on the forum
with so much patience and in detailed manner.
Sorry, I am out of office for this week but I will surely check these suggestions by you and @armingrudd

and will let you know if it works for me,

Regards,
Vishal Pathak

1 Like

Hi @armingrudd , Thank you for giving my issue a priority in your busy schedule
It really means a lot to me

and I will let you know if your suggestion works for me, Its just that I am out of office for this week

Regards,
Vishal Pathak

1 Like

Thanx for the component! superuseful. You made my day.

3 Likes

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