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.
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
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.
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.