JSON to table with nonstandard version of JSON

KNIME Analytics Platform
1

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:
[[“STNAME”,“POP”,“DATE_”,“state”],
[“Alabama”,“4849377”,“7”,“01”],
[“Alaska”,“736732”,“7”,“02”],
[“Arizona”,“6731484”,“7”,“04”],
[“Arkansas”,“2966369”,“7”,“05”],
[“California”,“38802500”,“7”,“06”]]

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?

2

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:

3

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

4

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:

5

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.

6

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:

image
image1303×1097 197 KB

2 Likes
7

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