JSON formating

[summer_le]

Hi, I need some help with transforming my data so it appears in a specific way in JSON format.

Without too much explanation behind why I need this format, can anyone help me with how I can get it to look like this:

[{
“category”: {
“set1”: 12345,
“set2”: 67890,
“Outsource”:{“set3”:1122}
}
}]

My problem is I am stuck on how to get the third line working where I need the values after 'Outsource" to also include the array and in speech marks like that.

I have also attached my simple Knime workflow and you can see the issue I have if I include the “” as a constant in the table.
JSON format.knwf (6.6 KB)

This is what I have so far. The speech marks is resulting in backslash being added and I am sure there is a very good reason for this behaviour and I just don’t know how to have it look like how I need.

If anyone can help me, it would help me a lot.

Thank you.

[Alex_JW]

Hey summer_le,

i think 3 times table to JSON looks a little bit weird, but that would be my best fast guess on your problem. Let me know what you think
Alex

E: Fixed naming and types to be numbers

My solution

[ArjenEX]

In the linked WF you have a duplication which @summer_le does not have in this requested output

An alternative approach is to use the rowID as root.

WF:
JSON format edit.knwf (27.3 KB)

[Alex_JW]

Hey ArjenEX,
i am not sure how you got that output I edited the workflow to change some wording. Should be fine now

Alex

[summer_le]

Hi @Alex_JW & @ArjenEX,

Thank you so much. I made a slight change where in the table creator, I changed the type for set3 to be integer because I realised when I had it as a string it would place speech marks around the 1122 and I needed it without the speech marks.

One thing I realised was (and I omitted this to have a smaller sample) - my data table actually has many rows so the RowID node threw an error once I had more than 1 record in the Table Creator.

I selected the ‘Ensure uniqueess’ check box but that means the category heading now has ‘category (1)’ ’ category (2)" and so on…

How can I alter this workflow to be able to handle multiple rows without changing the “category” name?
JSON format edit_multirow.knwf (14.7 KB)

[Alex_JW]

Hey @summer_le ,
it took me forever to realize what @ArjenEX meant. The outsource has to be inside “categorie” and i did not spot that, sorry. With one row it now looks like this:

[
  {
    "categorie": {
      "set1": "12345",
      "set2": "67890",
      "Outsource": {
        "set3": 1122
      }
    }
  }
]

and with many rows like this:

[
  {
    "categorie": {
      "set1": "12345",
      "set2": "67890",
      "Outsource": {
        "set3": 1122
      }
    }
  },
  {
    "categorie": {
      "set1": "12346",
      "set2": "67891",
      "Outsource": {
        "set3": 1123
      }
    }
  }, {...}, {}, ...
]

Here is the workflow

I hope that is the solution you seek
Alex

[summer_le]

This worked perfectly!! Thank you.