Json Path PK issues

Hi there,

I´ve been going over this flow over a few nights (new user here).

I´ve been having issues since from the JSON format I get 2 different tiers or leafs as you call it of data:

Tier 1: Main Product ID, Description, FoodNutrients (“[…]” string in which there is the subset of data/Tier 2)

Tier 2( FoodNutrients): number, name, amount, unitname (many to 1 relationship with Tier 1)

So my isue is that I want a table with father-son relationship where the Tier 1 is duplicated and the only difference is the Tier 2 per the many foods that constitute a product so then I can apply a group node and that´s where I´m having issues, no JSON Path or Ungroup methods have been working thus far.

(had to remove some screenshots due to the limit, let me know if you need anything to post afterwards)

Apologies for not posting the workflow as I have a api key within the GET Request node, removing it would make understading it worse than the SS’s and sharing a API key even from a public API is not a good thing in my mind.

Thank you in advance for all the insights.

Hi @FIKE

Welcome to the KNIME Community!

I find it hard to understand what your expected output is and where the issue is exactly. It looks like you want to extract the contents of FoodNutrients[] and keep it in the same row as fdcID, description and ID?

If you put the output of the GET Request in a Table Creator node and replace the get node, upload the workflow and draft your expected output for one or two records then it’s very likely you’ll receive some help :slight_smile:

1 Like

@FIKE you will most likely have to extract father and son separately and join later or you will have to place an ungroup behind the first json extract and carry the columns over to the second path.

Maybe you can provide an example representing your problem without spelling any secrets.

Heya @ArjenEX and @mlauber71 ,

Thank you so much for the kind message and feedback.

Finally had the time to do as you suggested. File is attached

GroupingIssues 1.knwf (39.1 KB)

In regards as said I want father and son joined but the ungroup/group methods or json paths arent going well thus far. The end table is basically the father id and description repeated as per many son rows so then I can GROUP BY and retrieve whatever metrics I need from that as the main table of data.

Thank you for your time.

Thanks @FIKE

This should probably get you in the right direction.

Main trick I used is a joiner node based on RowID. The number of rows between father and son is always the same, even when the query returns null so that ensures that the correct records are associated with each other.

The rest is usage of nodes that you already have encountered.

See WF:
Json Path PK issues.knwf (255.0 KB)

Hope this helps!

2 Likes

That was it, it seems so easy now! I thought you had to make join keys on both sides and then apply the join node.

Thank you so much for the help! Have a great weekend!

2 Likes

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