Hello, I am trying to parse a JSON to a table with JSON Path (ive already tried JSON to Table but it does not work because it freezes). I think the problem is with the JSON file structure because it does not has arrays.
So here im splitting by the 3 main parts of the file: evolution, states and groups, what i need to do now is for each of these parts get (for example in groups) id and description:
But here i dont have and array so i cant use [*] in JSON path or ungroup, also i cant figure out how to get the key (for example 3).
I have a similar problem with states and evolution.
Thank you for you time, regards!
edit: i forgot to say that ive already tried JSON to XML and XPath but it says “xml document structure must start and end in the same…” error and i think its a bug because it happens with others XML files using XML reader and we’ve checked those files and are ok.
Welcome to the Forum, @bcocitto! It’s always great to have some data to try things out
Indeed, the JSON to Table node takes quite long, I suspect since it would give a column for each key (dependent on the configuration) - so the JSON Path node is more feasible.
I’d split the evaluation into three branches, one each for evolucion, provincias and grupos. Then I’d use a JSON Path again to get the keys (by ticking the “Paths” option at the far right) and the values, like so:
(find this WF on the Hub).
You can then ungroup and e.g. with the string manipulator then beautify the keys and add another JSON Path node for the remaining JSONs, if needed.
To find a suitable JSONPaths I’d recommend websites like jsonpath.com, which allow you to interactively tune your Path (note that there are different versions of JSONPath and it sometimes won’t translate 1 to 1 - it needs some fiddling).
In case you are more familiar with XML/XPath and want to investigate this route instead: Strange that it did not work out for you - it did for me. Could you specify the error and what you did more closely? Which KNIME Version are you using?
Hi LukasS, thank you for your reply and I apologise for the delay in my response.
I finally solved it by using a couple of Python Source nodes that each one runs a script on Python and using Pandas extract all the data I need and then I got a table on the output. I will try your solution that is cleaner.
BTW thank you for telling me how to extract the keys of a JSON, I swear i spent like 4 hours trying to figure it out haha.
On the other hand, I keep getting an error when i try the XPath node, even in your Workflow. The error appears when I try to configure the node.
The translation is “XML structure must start and end within the same entity”.
huh, this is strange: as @ipazin mentioned, this issue should be fixed by now. Could you please check the XML-Processing version from within “Help → Install New Software…”?
Strange that the same workflow works for me and for you with the same version etc. it doesn’t. You are on Windows 10, right, and re-execution of the JSON reader won’t help either? I’ll see if I can reproduce the issue if I set my language to spanish…
hahaha yes it looks much better! Thank you very much
Yes im using windows 10 21H1 right now, ive tried re executing the json reader but still not working. I dont really know if it has something to do with spanish. Ive tried some other xmls that i previosly checked with some validators online and the xpath does not work too
