JSON to table with nonstandard version of JSON

I am using the GET Request node to download data from the US Census API.
The US Census publishes its data using a nonstandard version of JSON.

The data I pull using the API is formatted like this:

In this example, I would like to turn the first array into the column headers, and each subsequent array would be a row under the column header. There would be 4 columns (STNAME, POP, DATE_, state), 1 header row, and 5 rows of data.

Is there any way to do this in Knime given the nonstandard JSON?

Hi @hgrundy,

i think you could remove all [ and ] from the result and just use the cell splitter node with “,” delimiter? :thinking:
At least for your example that should work :slight_smile:

How would you remove the characters from a JSON-typed column? Would you need to convert the JSON column to a string?

yes I would go

  1. json->string
  2. string replacer [ ] to nothing
  3. cell splitter

however there might be a better direct json solution - i just never used much of the knime json nodes… so i cannot say :man_shrugging:

Okay, this may be really easy, but how do you turn the JSON field into a string? I’ve tried everything I can think of and can’t get a valid output that I put into a string replacer node.

In case anyone else faces this issue, this workflow worked for me in the end, but there may be a better way to do it:


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