JSON API Call & Transpose [Statistics Canada Example]

[wisemanleo]

I have a basic process that calls data from Statistics Canada (aka “StatCan”). The data pull appears successful, but I’m having trouble understanding what to do next to get the data in a table I can use.

Here is the overall process:

Here is the resulting view, bare in mind that the rows are actually columns:

The desired result is such that essentially I should have date in one column, and the values in the second column (which in this case, appears to be in two places: lower and upper bound).

Here are my settings:
Table Creator
https://www150.statcan.gc.ca/t1/wds/rest/getDataFromVectorByReferencePeriodRange?vectorIds=41691162,41713464&startRefPeriod=2016-01-01&endReferencePeriod=2017-01-01

Get Request

JSON to Table

Table View

Advice and help would be appreciated.

[wisemanleo]

KNIME community - if I can help provide any additional clarification on my ask, please let me know. Thank you in advance.

[Kathrin]

Hi @wisemanleo,

welcome to the KNIME Community Forum

I created a small example workflow for you: Extract Info from JSON – KNIME Hub

The idea is to use the JSON Path node to define the values you are interested in. You can add more values to the table by looking for the value in the JSON that you can see in the configuration window of the JSON Path node, selecting the value and clicking on the Add collection query button.

Please have a look and let me know in case of any questions

Cheers
Kathrin

[wisemanleo]

Hi Kathrin - appreciate the welcome and response. The small workflow worked as expected! Thanks again.