Iterate over related, numbered columns and turn them into a list of JSON objects?

I have a dataset that contains a series of columns that are numbered, where four columns that are related will have the same number and then the next four will be numbered with the subsequent integer. Example:

Identifier_1.id Identifier_1.Type Code Identifier_1.State Identifier_1.Issuer Identifier_2.id
123456 1 GA AETNA 7891011 ?

These go up to integer 50, so in total there are 200 columns. I want to try to combine these into a list of JSON objects in a single column. The output would look something like this:

[
    {
        "id" : 123456,
        "Type Code" : 1,
        "State" : "GA",
        "Issuer" : "AETNA"
    },
    {
        "id" : 7891011,
        ...
    }
]

Is this possible in KNIME, and if so how would I go about doing it?

Hi @rosstripi and welcome back to the KNIME forum,

Here is an example approach to do this:

26360-1-1.knwf (20.4 KB)

:blush:

3 Likes

So this worked, and I was able to adapt it using a Column List Loop to get it to work for multiple rows so that each list of JSON objects for each row would be stored in separate rows.

However, it breaks when there are rows that have empty cells or mismatched dimensions. Example:

Identifier_1.id Identifier_1.Type Code Identifier_1.State Identifier_1.Issuer Identifier_2.id
123456 1 GA AETNA 7891011 ?
45678 2

and here’s the specific error I get:
image

Any idea how to work past this?

In the Loop End configurations, check the “Allow variable column types” option.

:blush:

3 Likes

Awesome! That did it.

I have one more question: if I have an empty row, how do I make sure the resulting JSON list is an empty list rather than a list of empty objects?

Right now, when I have an empty row, it ends up looking like this:

[
  {
  },
  {
  }
]

but I need it to look like this:

[]

I’ve attached my flow so you can see why I’m getting that. It’s in Row2.

KNIME_project6.knwf (46.0 KB)

Alternatively, an empty cell would work as well as an empty list.

You can use the Remove Empty Rows node before the Table to JSON node.

:blush:

2 Likes

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