First, let me say that I know this is not a new topic. I have read the multiple other posts on this, downloaded the sample workflows to try to make it work, and haven’t been able to achieve my goal through trying:
- JSON to Table Node
- The Ungroup Node
- JSON Path Node
- Converting JSON to XML and using XPATH
What I am trying to do seems very simple and common for processing JSON, but I can’t seem to make it work right. I have data in the form below:
I am trying to simply have a flat table output with the following columns:
The multiple levels of hierarchy in the JSON are complicating retaining the integrity across my rows as I need to repeat season, seasonType, week, and poll for the more granular rank, school, conference, firstPlaceVotes, and points values. It seems the JSON Path Node was built to do this through collection queries, but when I try to use this I lose integrity between queries.
The answer may be very simple so apologies if I am “not getting it”, but I have spent hours on what should be a basic ETL challenge and remain unsuccessful. Can anyone help guide me as to what I am doing wrong?
The actual data file is attached in case there are incorrect JSON structures I am not seeing which has been the issue in a number of other posts.
Ranking JSON Data File (93.8 KB)
Thanks in advance for any help on this.