JSON manipulation

[ArminFan]

Hello, I want to create the following json file out of my data.

{
“CompanyCode”: “0001”,
“DocumentDate”: “2019-09-09T00:00”,
“PostingDate”: “2019-09-09T00:00”,
“to_SuplrInvcItemPurOrdRef”: [
{
“SupplierInvoiceItem”: “1”,
“PurchaseOrder”: “4500085616”,
“PurchaseOrderItem”: “10”,
“IsNotCashDiscountLiable”: false,
“to_SupplierInvoiceItmAcctAssgmt”: [
{
“SupplierInvoiceItem”: “1”,
“OrdinalNumber”: “2”,
“CostCenter”: “CC1234”,
“ControllingArea”: “0001”
}
]
}
]
}

I’m currently trying to combine the lower part from /“to_SupplierInvoiceItmAcctAssgmt” with the upper part from /“to_SuplrInvcItemPurOrdRef”. I try it via the json column

(there are more entries below, but they don’t contribute to this solution) and multiple value columns.
Then I use the table to json column as

But then there is always this “JSON” key in between (which is the column name), but I don’t need that at all:

Does someone have a quick answer to this?
Thanks.

Regards
Armin

[Daniel_Weikert]

If possible paste your sample workflow here. That makes it easier for people to support
br

[ArminFan]

@Daniel_Weikert

knime_forum_json.knwf (84.0 KB)

As you can see in the last node I get:

But I don’t want the JSON key there.

Regards,
Armin

[bruno29a]

Hi @ArminFan , I checked the data from your workflow. Is there any reason why you need the elements in to_SupplierInvoiceItmAcctAssgmt in an array? Based on what you built, you will always have just 1 set of these elements, is that correct?

I can modify your workflow so that that it gives you this:

It will need additional manipulation to add the array.

EDIT: I added the additional manipulation. Basically I added a GroupBy to convert them to a list which is recognized as an array in JSON. If you don’t need the elements to be in an array, you just need to remove the GroupBy (and you’ll get the results of my first screenshot above).

Results after adding the GroupBy:

Here’s your modified workflow: JSON manipulation.knwf (83.5 KB)

[ArminFan]

Hi @bruno29a ,

Yes, unfortunately I need that list because there could be more than just one item. But thanks, your solution works for me!

Best regards
Armin