I am trying to do a simple thing: convert a JSON usually found in open data portals to further process the metadata with KNIME. However, I cannot get this to work:
If tried JSON Path, where I want to select the users. The result is a List (collection of JSON). But when I then try to add a JSON to Table node, it just gets me the original “body” node, not the new users node.
When I try to directly connect the JSON to Table node to the GET request, I can manage, but then the resulting table is not organized in order of the JSON file, and only some columns are named with #1, #2, etc. others are not.
The JSON from the above example is not so complex, so this cannot be that hard. Can anybody help me out?
I have the same issue. Not sure what is going on except to note that for whatever reason the data in the packages collection query seems to be quite large (as opposed to what showed up in the simpler users example above).
For example, if you filter down the packages collection query to a single row after the ungroup node, the JSON to Table node will finish, although it takes a while - eventually producing a table with over 41,000 columns.
Maybe the initial collection query is malformed somehow? There’s a LOT of data there.
You’re returning the paths instead of the values in the JSON Path node. Ungrouping that list ungroups it into multiple rows, while also copying the original JSON unmodified into all rows as well. Doing a JSON to Table node on 745 copies of that already somewhat large JSON probably froze your machine (mine as well )
Instead you’d want to return the values, and also return them as type JSON instead of String. Ungrouping that gives you 745 rows with a different package each. JSON to Table then gives a reasonable amount of 67 columns, though I’m not sure that’s exactly what you want? You might want to query your data manually, or chain another JSON Path to get the nested data.
Thank you all for the very fast and successful help! I will try to improve this and share it as a workflow on the KNIME hub. Harvesting metadata from open data portals is something that potentially man other uses can be interested in as well!
The next step for me is to find ways to get a better overview over the data!
btw. this works in basically all open data portals, because 90% of them use CKAN. So you could use this on govdata.de or other other open data portals: API guide — CKAN 2.9.5 documentation
)
