Using JSON to table

KNIME Analytics Platform
1

Hi,

I have a json table where I need to extract PO line items as separate rows. Each line needs to include the PO header details. I am using the JSON Path node and Ungroup node. But am unable to extract the data correctly. When there are multiple PO’s it repeats each POLineItem for every PO. So the example below if there was another PO with 3 lines. Instead of a total of 6 lines (3 for each PO) I am get 12 lines.

[

{

"PONumber" : "PO295",

“date" : "2019-02-25",

"Vendor" : "V9999",

[

{

"POLineItem" : "POD1527",

"qty" : "1.00",

"uom" : "EACH",

"unitprice" : "47.60"

},

{

"POLineItem" : "POD1528",

"qty" : "1.00",

"uom" : " EACH ",

"unitprice" : "53.00"

},

{

"POLineItem" : "POD1529",

"qty" : "1.00",

"uom" : " EACH ",

"unitprice" : "22.00"

}

]

]

The resulting table should look like this:

PONumber date Vendor POLineItem qty uom unitprice

PO295 2019-02-25 V9999 POD1527 1.00 EACH 47.60

PO295 2019-02-25 V9999 POD1528 1.00 EACH 53.00

PO295 2019-02-25 V9999 POD1529 1.00 EACH 22.00

Any thoughts would be great.

2

Did you take a look at the JSONPath node? It should allow you to exactly this:

best,
Gabriel

3

Thats the one im using. I just cant work out how I can get the POLineItem to be specific to the PO. it just repeats all POLineItems for each PO.

4

Hi @acseh,
I created an example workflow for you, that shows how you can decompose nested JSON documents such as the one you posted using collection queries: JSON extraction.knwf (33.8 KB)

best,
Gabriel

2 Likes
5

Hi @acseh
Hi Gabriel,

here is a shorter solution JSON extraction.knwf (22.3 KB)

See you in Berlin
Andreas

2 Likes
6

Thank you so much. Works perfectly

7

Thank you. This is a great solution. I almost had it but started to get confused with the JSON path details.

8

@stan59,
nice solution, I had totally forgotten the Ungroup Node …

best,
Gabriel

9

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