Create Empty Complex (but predictable columns) Table

I’m reading OpenWeather forecast data… from JSON I’m getting optional columns (rain & snow)… The data where I’m at now looks like:
zipcode (string)
dt_0 (integer)
dt_txt_0 (string)
humidity_0 (decimal)
temp_0 (decimal)

[rain_0] <- Optional data
[snow_0] <- Optional data

                                          About 15ish columns in each group

Then each group is duplicated 40 times (0 to 39)… so
dt_0
dt_1
dt_2…
dt_39

I’d like to either:

  1. Best: pivot all the columns into a table row structure like:
    • zipcode
    • dt
    • humidity
    • temp
  2. Better: create an empty table with all possible columns (including optional columns) and combine the table defn with the actual data. I’d like to create the table problematically in Java or Python to help with the redundancy so I can cut-n-paste the groups and just fix the suffixes.
  3. Request a feature to the “Table Creator Node” that will allow me to cut-n-paste row headers and fix the suffixes.

Hoping someone may have come up with a simpler way of doing this before without manually creating (15
40 = 600) columns.

I’ve tried to build a solution with this file: http://bulk.openweathermap.org/sample/hourly_16.json.gz

Whether or not it has the same structure, you should be able to adapt it to your needs. The file contains one JSON per row. The format of a single JSON is like this:

{
“city” : {
“id” : 2995622,
“name” : “Marmoutier”,
“country” : “FR”,
“coord” : {
“lon” : 7.38195,
“lat” : 48.690048
}
},
“time” : 1489450159,
“data” : [ {
“dt” : 1489438800,
“main” : {
“temp” : 277.87,
“temp_min” : 275.39,
“temp_max” : 277.87,
“pressure” : 1001.26,
“sea_level” : 1043.91,
“grnd_level” : 1001.26,
“humidity” : 82,
“temp_kf” : 2.48
},
“weather” : [ {
“id” : 800,
“main” : “Clear”,
“description” : “sky is clear”,
“icon” : “01n”
} ],
“clouds” : {
“all” : 0
},
“wind” : {
“speed” : 0.92,
“deg” : 49.0012
},
“sys” : {
“pod” : “n”
},
“dt_txt” : “2017-03-13 21:00:00”
},
etc… ]
}

  1. Read the file with the File Reader node (make sure to not identify " as quote character)
  2. Use a String to JSON node
  3. Use a JSON Path node to extract id, name, country, coordinates and time with single queries and the objects from the “data” array with a collection query
  4. Use a Ungroup node to get one row per object in the just created list column
  5. Use a JSON to Table node to create one column per name/value pair

This will give you a nice table with the weather data for the different city/timestamp pairs. For clarification I’ve attached the workflow for you. jsonToTable.knar (578.2 KB)

Thank you so much… this is a big help!!