Forum Discussion

MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
3 years ago

Fetch JSON from an array

I'm trying to Fetch JSON Weather data. I have read all the documentation I can locate and I'm close but not quite there.

The API call returns JSON with what seems to be header type info and then an array of 8 days of forecast weather.

This is the public API Call http://api.openweathermap.org/data/2.5/onecall?lat=45.0&lon=-79.6&exclude=current,minutely,hourly,alerts&units=metric&appid=de24bdce24cd4ee879fc18e05f27e5af

Here is the sample response
{
"lat": 45,
"lon": -79.6,
"timezone": "America/Nipigon",
"timezone_offset": -14400,
"daily": [
{
"dt": 1647795600,
"sunrise": 1647775291,
"sunset": 1647819010,
"moonrise": 1647829920,
"moonset": 1647779880,
"moon_phase": 0.58,
"temp": {
"day": 2.76,
"min": 0.32,
"max": 5.41,
"night": 1.07,
"eve": 2.78,
"morn": 0.8
},
"feels_like": {
"day": -1.21,
"night": -1.67,
"eve": -0.94,
"morn": -3.39
},
"pressure": 1012,
"humidity": 86,
"dew_point": 0.66,
"wind_speed": 5.04,
"wind_deg": 292,
"wind_gust": 11.22,
"weather": [
{
"id": 616,
"main": "Snow",
"description": "rain and snow",
"icon": "13d"
}
],
"clouds": 100,
"pop": 0.86,
"rain": 0.28,
"snow": 0.15,
"uvi": 0
},
{
"dt": 1647882000,
"sunrise": 1647861577,
"sunset": 1647905486,
"moonrise": 1647921060,
"moonset": 1647867720,
"moon_phase": 0.62,
"temp": {
"day": 4.43,
"min": -1.41,
"max": 4.43,
"night": -1.41,
"eve": 1.51,
"morn": 0.9
},
"feels_like": {
"day": 1.31,
"night": -3.71,
"eve": -1.25,
"morn": -2.32
},
"pressure": 1022,
"humidity": 59,
"dew_point": -3.01,
"wind_speed": 4.19,
"wind_deg": 290,
"wind_gust": 8.7,
"weather": [
{
"id": 803,
"main": "Clouds",
"description": "broken clouds",
"icon": "04d"
}
],
"clouds": 67,
"pop": 0,
"uvi": 0
},
{
"dt": 1647968400,
"sunrise": 1647947864,
"sunset": 1647991962,
"moonrise": 0,
"moonset": 1647955860,
"moon_phase": 0.66,
"temp": {
"day": 3.82,
"min": -3.78,
"max": 5.56,
"night": 0.29,
"eve": 2.53,
"morn": -3.78
},
"feels_like": {
"day": 1.67,
"night": -3.68,
"eve": 0.06,
"morn": -6.97
},
"pressure": 1026,
"humidity": 33,
"dew_point": -11.02,
"wind_speed": 3.72,
"wind_deg": 79,
"wind_gust": 10.29,
"weather": [
{
"id": 803,
"main": "Clouds",
"description": "broken clouds",
"icon": "04d"
}
],
"clouds": 74,
"pop": 0,
"uvi": 0
},
{
"dt": 1648054800,
"sunrise": 1648034150,
"sunset": 1648078438,
"moonrise": 1648012200,
"moonset": 1648044420,
"moon_phase": 0.69,
"temp": {
"day": 4.56,
"min": -1.75,
"max": 4.56,
"night": 1.58,
"eve": 1.2,
"morn": -1.75
},
"feels_like": {
"day": 0.07,
"night": -3.78,
"eve": -4.81,
"morn": -7.24
},
"pressure": 1013,
"humidity": 47,
"dew_point": -5.71,
"wind_speed": 8.12,
"wind_deg": 105,
"wind_gust": 18.76,
"weather": [
{
"id": 616,
"main": "Snow",
"description": "rain and snow",
"icon": "13d"
}
],
"clouds": 80,
"pop": 1,
"rain": 11.79,
"snow": 0.24,
"uvi": 0
},
{
"dt": 1648141200,
"sunrise": 1648120437,
"sunset": 1648164914,
"moonrise": 1648103160,
"moonset": 1648133580,
"moon_phase": 0.73,
"temp": {
"day": 8.4,
"min": 1.77,
"max": 8.4,
"night": 3.73,
"eve": 3.41,
"morn": 4.75
},
"feels_like": {
"day": 5.7,
"night": 1.75,
"eve": 1.52,
"morn": 1.23
},
"pressure": 1008,
"humidity": 70,
"dew_point": 3.31,
"wind_speed": 5.97,
"wind_deg": 125,
"wind_gust": 15.88,
"weather": [
{
"id": 501,
"main": "Rain",
"description": "moderate rain",
"icon": "10d"
}
],
"clouds": 100,
"pop": 1,
"rain": 5.21,
"uvi": 0
},
{
"dt": 1648227600,
"sunrise": 1648206723,
"sunset": 1648251390,
"moonrise": 1648193700,
"moonset": 1648223460,
"moon_phase": 0.75,
"temp": {
"day": 2.3,
"min": 0.48,
"max": 3.34,
"night": 0.48,
"eve": 0.67,
"morn": 0.79
},
"feels_like": {
"day": -1.33,
"night": -3.13,
"eve": -2.95,
"morn": -2.89
},
"pressure": 1005,
"humidity": 92,
"dew_point": 1.14,
"wind_speed": 3.82,
"wind_deg": 245,
"wind_gust": 8.75,
"weather": [
{
"id": 616,
"main": "Snow",
"description": "rain and snow",
"icon": "13d"
}
],
"clouds": 100,
"pop": 1,
"rain": 6.34,
"snow": 1.77,
"uvi": 0
},
{
"dt": 1648314000,
"sunrise": 1648293009,
"sunset": 1648337865,
"moonrise": 1648283580,
"moonset": 1648314000,
"moon_phase": 0.8,
"temp": {
"day": 3.17,
"min": -0.84,
"max": 3.17,
"night": -0.84,
"eve": 1.27,
"morn": -0.08
},
"feels_like": {
"day": 0.43,
"night": -5.47,
"eve": -1.79,
"morn": -2.64
},
"pressure": 1012,
"humidity": 68,
"dew_point": -2.19,
"wind_speed": 4.22,
"wind_deg": 346,
"wind_gust": 9.29,
"weather": [
{
"id": 600,
"main": "Snow",
"description": "light snow",
"icon": "13d"
}
],
"clouds": 100,
"pop": 0.62,
"snow": 1.47,
"uvi": 0
},
{
"dt": 1648400400,
"sunrise": 1648379296,
"sunset": 1648424341,
"moonrise": 1648372740,
"moonset": 1648404900,
"moon_phase": 0.84,
"temp": {
"day": 1.21,
"min": -4.25,
"max": 2.19,
"night": -0.58,
"eve": -1.32,
"morn": -3.79
},
"feels_like": {
"day": -2,
"night": -2.75,
"eve": -1.32,
"morn": -6.51
},
"pressure": 1022,
"humidity": 52,
"dew_point": -7.64,
"wind_speed": 3.16,
"wind_deg": 356,
"wind_gust": 8.1,
"weather": [
{
"id": 804,
"main": "Clouds",
"description": "overcast clouds",
"icon": "04d"
}
],
"clouds": 99,
"pop": 0,
"uvi": 0
}
]
}

I note that there is an array called daily which begins on line 6 of the sample response above.

My problem is this.  If I include the JSON Records Path of

/daily

then the Pipeline writes out 8 blank records.

If I omit that JSON Records Path of /daily then the result is writing out 1 record with correct data, but just for the first entry of the 8 day array

How to I get the Pipeline to write out 8 records with 8 days of forecast weather data from the array?








------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
  • Hi Mark,

    We had a similar challenge and weren't able to resolve it, although it feels like it should be possible as it's not an uncommon requirement.

    We wanted to iterate over one level of the JSON and pass the result to another step to iterate over a lower level (which we've done easily in ETL tools), but couldn't find a combination of built in steps to do this. The 'iterate over JSON records' step only allows input from a 'Fetch JSON' step - if it could accept input from any step that generates JSON I think the problem would be solved. I tried playing around with the Regex step, but it wasn't satisfactory and we stopped at that point. 


    ------------------------------
    Jeremy Anson
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      Thx for that feedback.  I believe in my use case there is just one level of iteration,  unlike your use case.  I have also asked for help from Quickbase  Support and will post back with their response.

      ------------------------------
      Mark Shnier (YQC)
      mark.shnier@gmail.com
      ------------------------------
      • JeremyAnson's avatar
        JeremyAnson
        Qrew Cadet
        You could try replacing the schema with the content within the 'daily' node:

        {
        "dt": 1647795600,
        "sunrise": 1647775291,
        "sunset": 1647819010,
        "moonrise": 1647829920,
        "moonset": 1647779880,
        "moon_phase": 0.58,
        "temp": {
        "day": 2.76,
        "min": 0.32,
        "max": 5.41,
        "night": 1.07,
        "eve": 2.78,
        "morn": 0.8
        },
        "feels_like": {
        "day": -1.21,
        "night": -1.67,
        "eve": -0.94,
        "morn": -3.39
        },
        "pressure": 1012,
        "humidity": 86,
        "dew_point": 0.66,
        "wind_speed": 5.04,
        "wind_deg": 292,
        "wind_gust": 11.22,
        "weather": [
        {
        "id": 616,
        "main": "Snow",
        "description": "rain and snow",
        "icon": "13d"
        }
        ],
        "clouds": 100,
        "pop": 0.86,
        "rain": 0.28,
        "snow": 0.15,
        "uvi": 0
        }

        ... and keeping the path as /daily






        ------------------------------
        Jeremy Anson
        ------------------------------