Json Reader

Hi,

 

I am trying to read a Json file into knime. After I have chosen the path for my json file, when I want to see the json table, i am promted with "No data available". Also when I execute, I get the error "Execute failed: Expected end of input, but there were content: START_OBJECT".

If it helps, the structure of the json file is :

{......................{...}}

{......................{...}}

...and so on.

 

Any ideas?

Hello Stinger,

Can you verify that the structure is correct? For example you can use this website.

Because this does not look like a valid JSON structure as far as I know. Becaue you can not just list objects, you have to put them into an array:

[
{....{...}}, //<- mind the comma.
{....{...}}
]

Best,
Ferry

1 Like

Hi,

 

Thank you for your quick reply. I verified the structure and it`s a valid one, the answer is

Valid JSON (RFC 4627).

 

Hey,

In that case it would be helpful if you could provide a more detailed example of your data, because your previous example does not conform this standard. If you have sensible data you can replace the values with random strings/numbers, only the structure is important.

Thank you,
Ferry

Hello,

 

Ok, here is a sample. It has about 1000 entries, on each line, like :

{"abra":"heyoung","hour":"17:00","data":{"ind":1232435.5436,"iq":123}}

{"abra":"heyoung","hour":"17:00","data":{"ind":1232435.5436,"iq":123}}

{"abra":"heyoung","hour":"17:00","data":{"ind":1232435.5436,"iq":123}}

 

Thank you,

...which is not JSON standard.
You can not have multiple root elements.
To convert this to proper JSON and read it into KNIME you have to convert it to this format:

[
{"abra":"heyoung","hour":"17:00","data":{"ind":1232435.5436,"iq":123}},
{"abra":"heyoung","hour":"17:00","data":{"ind":1232435.5436,"iq":123}},
{"abra":"heyoung","hour":"17:00","data":{"ind":1232435.5436,"iq":123}}
]

Speak: a '[' at the beginning, a ']' at the end, and a comma after each line except the last one.

Best,
Ferry

allright,

made the changes and it works just fine now. Thank you for your input.

Btw, if you happen to know and automatic way to add a comma after each line, it would be of great use.

Have a nice day and thanks again!

You are welcome.

Sure, attached to this post.

Edit: I changed the workflow to work with variable table sizes, not only 3 rows.
Also: Use a File Reader to read in your JSON file.

Best,
Ferry

Trying to read in a json array and only getting a single row.  I would think that there would be two rows.  However it's adding each object in the array to it's own column.

I would like each array element to be it's own row, so I can loop through them.

[
{
"cvs": [
1,
2,
3,
4,
5,
6,
7,
8
],
"cnt": 8,
"recordate": "2016-03-01T00:00:00.000+00:00",
"cygroupid": 1,
"processed": 0,
"recordts": 1456790400
},
  {
    "cvs": [
      1,
      2,
      3,
      4,
      5,
      6,
      7,
      8
    ],
    "cnt": 8,
    "recordate": "2016-03-01T00:00:00.000+00:00",
    "cygroupid": 1,
    "processed": 0,
    "recordts": 1456790400
  }
]


Hi edmckee,

I am not sure how did you try, for me, the $[*] JSONPath work well for your example (it has been created two rows with the proper JSON objects within, without the array outside). Do you have some other use case, when the array should be present for both? (In that case I would recommend the JSON Transformer node to add the extra array.)

Cheers, gabor