I have very little experience with postgresql and was wondering how i can access certain fields from an array of json object so i can dynamically use them;

here is what the array i am trying to iterate looks like: ``

[
    {
      "days": [
        {
          "dew": -1.1,
          "icon": "snow",
          "snow": 3.2,
          "temp": 0.5,
          "precip": 3.7,
          "source": "fcst",
          "sunset": "17:36:42",
          "sunrise": "07:31:09",
          "tempmax": 1.2,
          "tempmin": -0.6,
          "uvindex": 0,
          "winddir": 220.3,
          "datetime": "2022-02-25",
          "humidity": 89.1,
          "pressure": 996,
          "stations": null,
          "windgust": 61.2,
          "feelslike": -6.2,
          "moonphase": 0.84,
          "snowdepth": 1.4,
          "windspeed": 39.2,
          "cloudcover": 95.7,
          "conditions": "Snow, Rain, Overcast",
          "precipprob": 95.2,
          "preciptype": [
            "rain",
            "snow"
          ],
          "severerisk": 10,
          "visibility": 6.9,
          "description": "Cloudy skies throughout the day with rain or snow clearing later.",
          "precipcover": null,
          "solarenergy": 1.2,
          "sunsetEpoch": 1645803402,
          "feelslikemax": -5.5,
          "feelslikemin": -6.6,
          "sunriseEpoch": 1645767069,
          "datetimeEpoch": 1645740000,
          "solarradiation": 11.6
        }
      ],
      "address": "Helsinki, Finland",
      "latitude": 60.1712,
      "timezone": "Europe/Helsinki",
      "tzoffset": 2,
      "longitude": 24.9327,
      "queryCost": 1,
      "resolvedAddress": "Helsinki, Etelä-Suomi, Suomi"
    }
  ]

i am trying to access the following fields: address, timezone, datetime, temperature, humidity, precipitation, wind, conditions.

And here is how i want to dynamically reference those fields inside my function (wdata is the array): https://i.stack.imgur.com/RhyhG.png

CodePudding user response：

PostgreSQL has the following JSON operators for accessing JSON fields: -> and ->>. You can read more about them here: https://www.postgresql.org/docs/9.3/functions-json.html

In your particular example (assuming wdata holds the JSON you posted above), you can access data like this

• wdata->0->>'address' - that is, fetch the first element from the array and then fetch its address field;
• wdata->0->'days'->0->>'humidity' - that is, fetch the first element from the array, then fetch the days field (which itself is an array), then fetch the first element of the nested array and finally fetch the humidity field of the element from the nested array;