如何在 postgresql 中迭代 json 对象数组并访问其元素
我对 postgresql 的经验很少，并且想知道如何从 json 对象数组访问某些字段，以便我可以动态使用它们；
这是我尝试迭代的数组的样子： ``
[
{
"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"
}
]我正在尝试访问以下字段：地址、时区、日期时间、温度、湿度、降水量、风力、条件。
这是我想要动态引用函数内的这些字段的方式（wdata 是数组）： https://i.sstatic.net/RhyhG.png
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.sstatic.net/RhyhG.png
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
PostgreSQL 有以下用于访问 JSON 字段的 JSON 运算符：
->和->>。您可以在这里阅读有关它们的更多信息： https://www.postgresql.org/ docs/9.3/functions-json.html在您的特定示例中（假设
wdata保存您上面发布的 JSON），您可以访问这样的数据wdata->0->>'address'- 即从数组中取出第一个元素，然后取出其address字段；wdata->0->'days'->0->>'humidity'- 也就是说，从数组中获取第一个元素，然后获取 days 字段（其本身是一个数组），然后获取嵌套数组的第一个元素，最后从嵌套数组中获取该元素的humidity字段；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.htmlIn your particular example (assuming
wdataholds the JSON you posted above), you can access data like thiswdata->0->>'address'- that is, fetch the first element from the array and then fetch itsaddressfield;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 thehumidityfield of the element from the nested array;