Nested JSON Extract

CloverDX Platform
1

Hello together,

First: Thanks for that impressive software. :smiley:
Second: Sorry for my first (very Long) post:

I actually working on a project from my University (Course datamanagement :/). We got several tasks to develop Interfaces between different sources/destination.
First source is an csv file that we had to load into different database tables. Easy stuff and my SSIS know how was good enough to get that done in several minutes.
Last week our Tutor gave us the second Task to bring a json file to a database table Format. And this Task is pretty hard. The json file is heavily nested and the SSIS hit the Limit for that. So i took a look around and a customer from a company where i work used cloveretl. So i took a look on the CE Edition.
We got following json file:

We got following Detail Tasks:
1. Extract the NID on root element (json object)
2. Extract the nights on root element (json object)
3. Extract the title on the root element (json object)
4. Extract from waypoints (Array) the Arrival, departure
6. Extract from waypoints/Harbour the title and iata Code
7. Extract from sails (Array) the nid
8. Extract from sails (Array) the Arrival, the departure
9. transform the data from Task 8 from UNIX timestamp to normal date Format (dd-mm-yyyy)
10. Extract from sails (Array)/cabins (Array) the Arrival, the departure, The Kindname and the catalog price

And bring that to ONE line per (sails)nid in the database. Puhh

For Logical Understatement:
the json includes a ship cruise with (possible) different sails Dates and cabins/Prices per cabin (why i study Travel Management? :evil: ).

My actually point of solution
I build a graph with an json extract Reader and mapped the necessary fields in the XSD Mapper (freaking cool visual mapper!). I changed the fields which are from Arrays to a list of strings/numbers and wrote it to a Excel file (for testing). Works. But the results spread over different rows (logic) and columns.

So my first questions:
1. how can i merge the results in one or two lines
2. how can i logical map some list results together (for example first kindname and first catalog Price and so one)

Thanks for some tips

Best Regards
Timo

2

Try the Denormalizer component for turning multiple records into fewer. I’m not sure what you’re actually asking in your second question but there are a number of Joiner components that you might be able to use depending on what you’re trying to do.

3

Hi TimoCHH,

As anyeone mentioned the Denormalizer component would be the best suited for merging records together, unfortunately Community Version of CloverETL does not include this component. If possible can you please share you project an we can look for a possible alternative solution to your use case. Also, could you please go into more detail on the second question.