Json conversion

[ace2131]

HI
I am trying to convert the attached json (text) to a table but I am facing problems. I am having a particular problem when I try to extract columns beyond the retweeted_status. There are missing values and as soon as I try to ungroup them output gets messed up. Can someone help?

I have attached the file and my workflow.

Thanks

Acepage1.txt (640.4 KB)
KNIME_project6.knwf (13.5 KB)

[Andrew_Steel]

Hi @ace2131,

there are 2 wrong definitions in your JSON Path and a bigger problem with ‘null’ values in string definitions.

First, the ‘user geocoordinates1’ isn’t a double value, it’s an array with two double values.
Second, the ‘rt hashtagtexts’. What do you want to achieve with this definition? You get the values with the following ‘rt hashtext[123]’ definitions. Remove the ‘rt hahstagtexts’ or add a collection query for the ‘hashtags’, because that’s an array of hashtags.
If you do this two steps, you get your 100 rows.

But I said there is a bigger problem: What is the representing of ‘null’ in JSON? You find a discussion about this problem here
It seems, that the JSON Path Node in KNIME ignores the null values. As an example, if you take a look in the output from JSON Path you find in column ‘in_reply_to_status_is_strs’ and in column ‘in_reply_to_user_is_strs’ only three values, no missing values

. Thats because the type definitions of this JSON Values are string. If you define ‘null’ values as JSON types, you get the right list type with missing values. But there are so many string columns with null values in the JSON file. As a workaround you can use a JSON To XML node followed by a XPath node to build your solution.

I hope it helps
Andrew

[ace2131]

HI Andrew

Thanks for the help.

First I looked whether declaring columns as NULL can be done but the problem is that the JSON path node doesn’t have an option to define the type as NULL.

Then I tried the XML method which you mentioned. Here too the problem persists that Knime is not recognizing missing values despite the fact I am declaring to Return Missing values. I have attached the flow. KNIME_project7.knwf (10.3 KB)

Am I doing something wrong or is there a some kind of bug in these knime nodes?

Thanks

Ace

[Andrew_Steel]

Hi Ace,

i don’t know if there is a bug in this nodes. It’s still a problem with the definition of null. Both ways works fine, if you go the long way. Dont’t solve it with one node. Split every arrays in rows. Start with results[*] in JSON Reader.

Nested JSON objects with null are not a problem but JSON arrays with null. So split it in lists.

KNIME_project7.knwf (10.5 KB)

Andrew

[ace2131]

Thanks Andrew, you are a star.

Suggestion you gave worked. Although I had to add multiple nodes to take care of multiple arrays your suggestion worked. Tested on several different files with different combinations and seems to be working. Thanks a lot for your help.