- Beiträge: 3
Komplexeres JSON abarbeiten
- Maximilian Bosche
- Autor
- Offline
- Benutzer
-
Weniger
Mehr
3 Wochen 4 Tage her #5327
von Maximilian Bosche
Komplexeres JSON abarbeiten wurde erstellt von Maximilian Bosche
Moin zusammen,
ich möchte eine Wetter API abfragen, die ein relativ komplexes, verschachteltes JSON zurückgibt. Und ich bin leicht am verzweifeln, mein nächster Schritt wäre, dass ich das mit einem .NET Skript realisiere. Bevor ich das mache wäre ich froh, wenn mir jemand einen Tipp geben kann, ob es auch ohne .NET Skript geht. Ich schmeiß das JSON hier mal rein.
Ich möchte das in eine SQL Tabelle schreiben, u.a. mit den Spalten:
Datum, Uhrzeit, TemperaturMin, TemperaturMax, TemperaturAvg, WindgeschwindigkeitMax, WindgeschwindigkeitAvg, ...
Wüsste aber nicht, wie ich .dates[0] zu .data.(object mit name "Wind Speed").values.avg[0] zuordnen könnte, um die WindgeschwindigkeitAvg zum Ersten TimeStamp zu ermitteln..
ich möchte eine Wetter API abfragen, die ein relativ komplexes, verschachteltes JSON zurückgibt. Und ich bin leicht am verzweifeln, mein nächster Schritt wäre, dass ich das mit einem .NET Skript realisiere. Bevor ich das mache wäre ich froh, wenn mir jemand einen Tipp geben kann, ob es auch ohne .NET Skript geht. Ich schmeiß das JSON hier mal rein.
Ich möchte das in eine SQL Tabelle schreiben, u.a. mit den Spalten:
Datum, Uhrzeit, TemperaturMin, TemperaturMax, TemperaturAvg, WindgeschwindigkeitMax, WindgeschwindigkeitAvg, ...
Wüsste aber nicht, wie ich .dates[0] zu .data.(object mit name "Wind Speed").values.avg[0] zuordnen könnte, um die WindgeschwindigkeitAvg zum Ersten TimeStamp zu ermitteln..
Warnung: Spoiler!
Code:
{
"dates":
[
"2020-01-01 00:00:00",
"2020-01-01 01:00:00",
"2020-01-01 02:00:00",
"2020-01-01 03:00:00",
"2020-01-01 04:00:00",
"2020-01-01 05:00:00",
"2020-01-01 06:00:00",
"2020-01-01 07:00:00",
"2020-01-01 08:00:00",
"2020-01-01 09:00:00",
"2020-01-01 10:00:00",
"2020-01-01 11:00:00",
"2020-01-01 12:00:00",
"2020-01-01 13:00:00",
"2020-01-01 14:00:00",
"2020-01-01 15:00:00",
"2020-01-01 16:00:00",
"2020-01-01 17:00:00",
"2020-01-01 18:00:00",
"2020-01-01 19:00:00",
"2020-01-01 20:00:00",
"2020-01-01 21:00:00",
"2020-01-01 22:00:00",
"2020-01-01 23:00:00",
"2020-01-02 00:00:00"
],
"data":
[
{
"name": "Solar Panel",
"name_original": "Solar Panel",
"type": "Sensor",
"decimals": 0,
"unit": "mV",
"ch": 4,
"code": 30,
"group": 7,
"serial": "x",
"mac": "x",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"last"
],
"values":
{
"last":
[
0,
0,
0,
0,
0,
0,
0,
0,
790,
5056,
6892,
6978,
6897,
6633,
4860,
1375,
0,
0,
0,
0,
0,
0,
0,
0,
0
]
}
},
{
"name": "Precipitation",
"name_original": "Precipitation",
"type": "Sensor",
"decimals": 1,
"unit": "mm",
"ch": 5,
"code": 6,
"group": 5,
"serial": "x",
"mac": "x",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"sum"
],
"values":
{
"sum":
[
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
]
}
},
{
"name": "Wind speed",
"name_original": "Wind speed",
"type": "Sensor",
"decimals": 1,
"unit": "m/s",
"ch": 6,
"code": 5,
"group": 6,
"serial": "x",
"mac": "x",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"avg",
"max"
],
"values":
{
"avg":
[
0.1,
0.4,
0.9,
1.2,
0.9,
0.3,
0.4,
0.4,
0.4,
0.2,
0.5,
0.7,
0.7,
1,
1,
0.8,
0.8,
1,
0.8,
1.3,
1.1,
1.2,
0.8,
0.5,
0.1
],
"max":
[
0.6,
1,
1.3,
2,
1.5,
1,
0.6,
0.8,
0.7,
0.8,
1,
1,
1.3,
1.6,
1.4,
1.2,
1.2,
1.5,
1.3,
1.7,
1.7,
1.5,
1.4,
1.3,
0.4
]
}
},
{
"name": "Battery",
"name_original": "Battery",
"type": "Sensor",
"decimals": 0,
"unit": "mV",
"ch": 7,
"code": 7,
"group": 7,
"serial": "x",
"mac": "x",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"last"
],
"values":
{
"last":
[
6491,
6494,
6494,
6494,
6494,
6494,
6471,
6479,
6468,
6522,
6608,
6693,
6642,
6667,
6616,
6588,
6499,
6499,
6468,
6468,
6445,
6462,
6462,
6459,
6431
]
}
},
{
"name": "HC Serial Number",
"name_original": "HC Serial Number",
"type": "Sensor",
"decimals": 0,
"unit": "",
"ch": 17,
"code": 508,
"group": 61,
"serial": "x",
"mac": "x",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"last"
],
"values":
{
"last":
[
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115,
20194115
]
}
},
{
"name": "HC Air temperature",
"name_original": "HC Air temperature",
"type": "Sensor",
"decimals": 2,
"unit": "°C",
"ch": 18,
"code": 506,
"group": 1,
"serial": "x",
"mac": "x",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"avg",
"max",
"min"
],
"values":
{
"avg":
[
1.84,
1,
0.2,
-0.28,
-0.96,
-1.29,
-1.62,
-1.61,
-1.77,
-1.69,
-1.81,
-1.67,
-1.41,
-0.99,
-1.05,
-0.97,
-0.86,
-0.85,
-0.85,
-1.19,
-1.49,
-1.84,
-2.11,
-2.11,
-2.2
],
"max":
[
2.14,
1.47,
0.41,
0.08,
-0.5,
-0.75,
-1.5,
-1.46,
-1.64,
-1.52,
-1.63,
-1.51,
-1.2,
-0.87,
-0.9,
-0.9,
-0.77,
-0.8,
-0.73,
-0.95,
-1.28,
-1.64,
-2.01,
-2.03,
-2.13
],
"min":
[
1.5,
0.53,
0.01,
-0.53,
-1.34,
-1.67,
-1.7,
-1.77,
-1.86,
-1.81,
-2,
-1.78,
-1.67,
-1.12,
-1.17,
-1.06,
-0.98,
-0.9,
-1.24,
-1.41,
-1.73,
-2.03,
-2.18,
-2.19,
-2.34
]
}
},
{
"name": "HC Relative humidity",
"name_original": "HC Relative humidity",
"type": "Sensor",
"decimals": 2,
"unit": "%",
"ch": 19,
"code": 507,
"group": 2,
"serial": "x",
"mac": "x",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"avg",
"max",
"min"
],
"values":
{
"avg":
[
99.58,
99.56,
99.55,
99.54,
99.53,
99.53,
99.53,
99.54,
99.54,
99.55,
99.55,
99.56,
99.56,
99.56,
99.56,
99.56,
99.56,
99.56,
99.56,
99.56,
99.57,
99.57,
99.58,
99.59,
99.59
],
"max":
[
99.58,
99.57,
99.55,
99.54,
99.54,
99.53,
99.53,
99.54,
99.54,
99.55,
99.56,
99.56,
99.56,
99.56,
99.56,
99.56,
99.56,
99.56,
99.56,
99.57,
99.57,
99.57,
99.58,
99.59,
99.6
],
"min":
[
99.57,
99.55,
99.54,
99.54,
99.53,
99.53,
99.53,
99.53,
99.54,
99.54,
99.55,
99.56,
99.56,
99.56,
99.56,
99.56,
99.56,
99.56,
99.56,
99.56,
99.57,
99.57,
99.57,
99.58,
99.59
]
}
},
{
"name": "Dew Point",
"name_original": "Dew Point",
"type": "Sensor",
"decimals": 1,
"unit": "°C",
"ch": 20,
"code": 21,
"group": 17,
"serial": "x",
"mac": "x",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"avg",
"min"
],
"values":
{
"avg":
[
1.7,
0.9,
0.1,
-0.3,
-1,
-1.3,
-1.6,
-1.6,
-1.8,
-1.7,
-1.8,
-1.7,
-1.4,
-1,
-1.1,
-1,
-0.9,
-0.9,
-0.9,
-1.2,
-1.5,
-1.8,
-2.1,
-2.1,
-2.2
],
"min":
[
1.4,
0.4,
0,
-0.5,
-1.4,
-1.7,
-1.7,
-1.8,
-1.9,
-1.8,
-2,
-1.8,
-1.7,
-1.1,
-1.2,
-1.1,
-1,
-0.9,
-1.3,
-1.4,
-1.7,
-2,
-2.2,
-2.2,
-2.3
]
}
},
{
"name": "Latitude",
"name_original": "Latitude",
"type": "Sensor",
"decimals": 6,
"unit": "m",
"ch": 21,
"code": 650,
"group": 62,
"serial": "x",
"mac": "x",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"last"
],
"values":
{
"last":
[
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909,
52.806909
]
}
},
{
"name": "Longitude",
"name_original": "Longitude",
"type": "Sensor",
"decimals": 6,
"unit": "m",
"ch": 22,
"code": 651,
"group": 62,
"serial": "x",
"mac": "x",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"last"
],
"values":
{
"last":
[
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036,
8.147036
]
}
},
{
"name": "Altitude",
"name_original": "Altitude",
"type": "Sensor",
"decimals": 1,
"unit": "m",
"ch": 23,
"code": 652,
"group": 62,
"serial": "x",
"mac": "x",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"last"
],
"values":
{
"last":
[
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5,
62.5
]
}
},
{
"name": "Horizontal dilusion of position",
"name_original": "Horizontal dilusion of position",
"type": "Sensor",
"decimals": 1,
"unit": "",
"ch": 24,
"code": 659,
"group": 62,
"serial": "x",
"mac": "x",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"last"
],
"values":
{
"last":
[
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4,
1.4
]
}
},
{
"name": "VPD",
"name_original": "VPD",
"type": "Sensor",
"decimals": 2,
"unit": "kPa",
"ch": 26,
"code": 25,
"group": 22,
"serial": "x",
"mac": "x",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"avg",
"min"
],
"values":
{
"avg":
[
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
],
"min":
[
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
]
}
},
{
"name": "Wind speed max",
"name_original": "Wind speed max",
"type": "Sensor",
"decimals": 1,
"unit": "m/s",
"ch": 31,
"code": 49,
"group": 66,
"serial": "x",
"mac": "x",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"max"
],
"values":
{
"max":
[
1.3,
1.5,
2.4,
3.9,
2.5,
1.9,
2,
1.4,
1.6,
1.5,
2,
2.5,
2.5,
3.2,
3.4,
2.6,
3.3,
3.9,
2.6,
3.7,
2.3,
2.4,
2.5,
1.7,
1.6
]
}
},
{
"name": "DeltaT",
"name_original": "DeltaT",
"type": "Sensor",
"decimals": 0,
"unit": "°C",
"ch": 35,
"code": 27,
"group": 68,
"serial": "x",
"mac": "x",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"avg",
"max",
"min"
],
"values":
{
"avg":
[
0,
0,
0,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1
],
"max":
[
0,
0,
0,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1
],
"min":
[
0,
0,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1,
-1
]
}
},
{
"name": "Sensor board battery",
"name_original": "Sensor board battery",
"type": "Sensor",
"decimals": 0,
"unit": "mV",
"ch": 16000,
"code": 33798,
"group": 7,
"serial": "x",
"mac": "D88039D07563",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"last"
],
"values":
{
"last":
[
3593,
3593,
3593,
3593,
3591,
3590,
3588,
3590,
3588,
3586,
3587,
3587,
3586,
3588,
3587,
3588,
3588,
3587,
3588,
3587,
3587,
3586,
3586,
3586,
3586
]
}
},
{
"name": "RF system - Received signal strength indication",
"name_original": "RF system - Received signal strength indication",
"type": "Sensor",
"decimals": 0,
"unit": "dBm",
"ch": 16007,
"code": 36359,
"group": 60,
"serial": "x",
"mac": "D88039D07563",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"avg",
"min"
],
"values":
{
"avg":
[
-76,
-77,
-78,
-80,
-78,
-76,
-74,
-72,
-73,
-73,
-73,
-74,
-75,
-76,
-75,
-76,
-76,
-78,
-79,
-78,
-78,
-77,
-76,
-76,
-75
],
"min":
[
-76,
-78,
-80,
-82,
-81,
-76,
-76,
-73,
-73,
-73,
-74,
-75,
-76,
-78,
-76,
-77,
-77,
-79,
-79,
-79,
-79,
-78,
-77,
-76,
-75
]
}
},
{
"name": "Air temperature",
"name_original": "Air temperature",
"type": "Sensor",
"decimals": 1,
"unit": "°C",
"ch": 16011,
"code": 16385,
"group": 1,
"serial": "x",
"mac": "D88039D07563",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"avg",
"max",
"min"
],
"values":
{
"avg":
[
3.1,
2.8,
2.4,
2.1,
1.7,
1.5,
1.2,
1,
0.9,
0.9,
0.9,
0.8,
0.9,
1.1,
1.1,
1.1,
1,
0.9,
0.9,
0.7,
0.5,
0.3,
0.2,
0.2,
0.1
],
"max":
[
3.3,
3.1,
2.6,
2.3,
2,
1.6,
1.3,
1.1,
1.1,
1,
0.9,
0.9,
1.1,
1.1,
1.2,
1.1,
1.1,
1,
0.9,
0.8,
0.6,
0.4,
0.3,
0.3,
0.3
],
"min":
[
2.9,
2.6,
2.3,
1.9,
1.5,
1.3,
1.1,
0.9,
0.8,
0.8,
0.8,
0.8,
0.9,
1,
1,
1,
0.9,
0.8,
0.9,
0.6,
0.4,
0.2,
0.1,
0.2,
0.1
]
}
},
{
"name": "Soil temperature",
"name_original": "Soil temperature",
"type": "Sensor",
"decimals": 1,
"unit": "°C",
"ch": 16014,
"code": 17153,
"group": 3,
"serial": "x",
"mac": "D88039D07563",
"registered": "2019-12-31 14:00:21",
"vals":
{},
"aggr":
[
"avg",
"max",
"min"
],
"values":
{
"avg":
[
2,
1.7,
1,
0.6,
0.2,
-0.3,
-0.5,
-0.5,
-0.7,
-0.5,
-0.6,
-0.6,
-0.5,
-0.1,
-0.1,
-0.1,
-0.1,
-0.1,
-0.1,
-0.4,
-0.6,
-0.9,
-1.1,
-1.1,
-1.2
],
"max":
[
2.3,
2,
1.3,
0.8,
0.6,
-0.1,
-0.3,
-0.5,
-0.5,
-0.4,
-0.5,
-0.6,
-0.3,
0,
0,
0,
0,
0,
0,
-0.3,
-0.5,
-0.8,
-1.1,
-1.1,
-1.1
],
"min":
[
1.8,
1.4,
0.8,
0.4,
-0.1,
-0.5,
-0.5,
-0.7,
-0.8,
-0.8,
-0.8,
-0.6,
-0.7,
-0.3,
-0.3,
-0.2,
-0.3,
-0.3,
-0.1,
-0.6,
-0.8,
-1.1,
-1.2,
-1.1,
-1.3
]
}
},
{
"name": "Sunrise",
"type": "Calculation",
"unit": false,
"values":
{
"result":
[
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577871575,
1577957967
]
}
},
{
"name": "Sunset",
"type": "Calculation",
"unit": false,
"values":
{
"result":
[
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577899311,
1577985776
]
}
},
{
"name": "Midnight",
"type": "Calculation",
"unit": false,
"values":
{
"result":
[
1577836800,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
1577923200
]
}
}
]
}
Bitte Anmelden oder Registrieren um der Konversation beizutreten.
- FlowHeater-Team
-
- Offline
- Administrator
-
Weniger
Mehr
- Beiträge: 2071
3 Wochen 4 Tage her #5328
von FlowHeater-Team
gruß
Robert Stark
FlowHeater-Team antwortete auf Komplexeres JSON abarbeiten
Hallo Herr Bosche,
im Prinzip ist es ganz einfach
Sie müssen lediglich die JSON-Datei über separate Verarbeitungsschritte mehrmals lesen und die Daten einzeln abgreifen sowie am Ende wieder zusammenbringen.
Für Ihre JSON-Daten benötigen Sie lediglich 2 Verarbeitungsschritte.
Im Anhang finden Sie ein kleines Beispiel dazu. Weitere Infos wie Sie JSON-Dateien Lesen und Schreiben können finden Sie hier: Beispiele zur JSON zu/nach CSV Konvertierung
im Prinzip ist es ganz einfach
Für Ihre JSON-Daten benötigen Sie lediglich 2 Verarbeitungsschritte.
- Im ersten Verarbeitungsschritt lesen Sie das „dates“ Array indem Sie im
JSON Adapter
im JPATH „/dates“ angeben. Diese Werte fügen Sie an eine
InMemory Adapter
Tabelle an. Damit Sie einen Bezug herstellen können benötigen Sie hier noch einen
AutoID Heater
, der von 1 bis N hochzählt. 1 = der erste Messwert, 2 = der zweite, …
- Im zweiten Verarbeitungsschritt lesen Sie das „data“ Array. Hier benötigen Sie wieder einen AutoID Heater sowie einen String Replace Heater , der Ihnen per Datenlookup das dazugehörige Datum vom „dates“ Array aus der InMemory Adapter Tabelle holt.
Im Anhang finden Sie ein kleines Beispiel dazu. Weitere Infos wie Sie JSON-Dateien Lesen und Schreiben können finden Sie hier: Beispiele zur JSON zu/nach CSV Konvertierung
gruß
Robert Stark
Bitte Anmelden oder Registrieren um der Konversation beizutreten.
Ladezeit der Seite: 0.286 Sekunden
