API: Get Request to JSON

KNIME Analytics Platform
1

There is an API for the British Office of National Statistics census data which I am trying to incorporate into Knime. The developer hub is here and I have followed the instructions with GET Request to return a JSON (URL: https://api.beta.ons.gov.uk/v1/datasets/TS008/editions/2021/versions/1/json)

The question now is: how do I actually turn that into data? I have tried JSON to table and it just returns over 1000 columns which not a lot in them. Thank you everyone :slight_smile:

2

Hi,
If you want specific fields from a JSON doc, I recommend the JSON Path node. JSON Path is a language for expressions used to extract data from JSON. You can find documentation and examples here. It’s very handy for turning JSON into tabular data. Sometimes it makes sense to use two JSON Path nodes in succession: one to get the elements that will turn into individual rows (collection query with output type JSON), then an Ungroup node to turn the list into rows, then another JSON Path to unpack the element-JSONs into multiple columns.
Kind regards,
Alexander

1 Like
3

Hi again,
Often it is also good to combine JSON Path with Ungroup and JSON to Table. I have created a small example workflow for your URL that extracts the dimensions.
Kind regards,
Alexander
British Stats.knwf (10.2 KB)

4 Likes
4

Thanks, that works! I have no idea what you did and would never have been able to figure that out myself, but I shall investigate and see if I can get my head around it.

5

Hi,
Happy to explain. The JSON to Table node just takes a document and turns it into a single row. This means nested JSON objects are turned into additional columns. Often, what you really want is to turn a list of objects from the JSON document into individual rows. This can be done by combining the JSON Path with the JSON to Table. The JSON Path extracts the individual list items into a KNIME collection cell, this is then turned into separate rows using Ungroup. Now you have one JSON document per list item. The JSON to Table now can properly unwrap those into columns.
Kind regards,
Alexander

2 Likes
6

Thank you. I get what it does now; I just need to figure out the path node and how that is laid out so I can do it myself in future! I should be able to figure it out with your example and trying a few myself.

1 Like
7

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