Need help: JSON to Influx

Hi, I love Node Red, because I am not able to programm my own code :wink:
I want to see my solar forecast in grafana (via influx).
I get the data JSON from solarprognose.de, but the influx node rejects it.
"unable to parse: invalid field name"
What must I do, to change the JSON, that it will work with influx?
This is the JSON: the values are "timestamp, power, energy"

{"1733727600":[1733727600,0,0],"1733731200":[1733731200,0.324,0.324],"1733734800":[1733734800,0.95,1.274],"1733738400":[1733738400,0.852,2.126],"1733742000":[1733742000,1.532,3.658],"1733745600":[1733745600,2.077,5.735],"1733749200":[1733749200,0.5,6.235],"1733752800":[1733752800,0.302,6.537],"1733756400":[1733756400,0,6.537],"1733814000":[1733814000,0,0],"1733817600":[1733817600,0.324,0.324],"1733821200":[1733821200,0.565,0.889],"1733824800":[1733824800,0.648,1.537],"1733828400":[1733828400,0.675,2.212],"1733832000":[1733832000,0.619,2.831],"1733835600":[1733835600,0.502,3.333],"1733839200":[1733839200,0.303,3.636],"1733842800":[1733842800,0,3.636]}

Thanks for any help.

If did a complete flow as test. Maybe it helps.

[{"id":"cd5910130d6f031b","type":"change","z":"5ad55a7b0d4580eb","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$zip(\t $keys(payload.data),\t $zip(\t payload.data.*#$i[($i%2)=0],\t payload.data.*#$i[($i%2)=1]\t )\t).\t {\t \"measurement\" : \"Solarprognose2\",\t \"timestamp\":$number($[0])*1000,\t \"fields\":{\t \"Datum\": ($moment($[0],'X').format('DD.MM.YYYY HH:mm:ss'))[0],\t \"Prognose_W\":$[1],\t \"Prognose_Wh\":$[2]}\t }\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":650,"y":2820,"wires":[["d15dac041a441f86","c4947de713be0f3d","690ba182ca09fbb8"]]},{"id":"d15dac041a441f86","type":"debug","z":"5ad55a7b0d4580eb","name":"Daten","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":810,"y":2740,"wires":[]},{"id":"ee1f3162147de5e3","type":"http request","z":"5ad55a7b0d4580eb","name":"","method":"GET","ret":"txt","paytoqs":"ignore","url":"https://www.solarprognose.de/web/solarprediction/api/v1?_format=json&access-token=xxxxxxxxxxxxxxx&item=location&id=3747&type=hourly","tls":"","persist":false,"proxy":"","insecureHTTPParser":false,"authType":"","senderr":false,"headers":[],"x":310,"y":2820,"wires":[["953203383cdb5a24"]]},{"id":"953203383cdb5a24","type":"json","z":"5ad55a7b0d4580eb","name":"","property":"payload","action":"","pretty":false,"x":450,"y":2820,"wires":[["cd5910130d6f031b","8c21582d4d69d761"]]},{"id":"bceeef4ce1883e59","type":"cronplus","z":"5ad55a7b0d4580eb","name":"","outputField":"payload","timeZone":"","storeName":"","commandResponseMsgOutput":"output1","defaultLocation":"","defaultLocationType":"default","outputs":1,"options":[{"name":"schedule1","topic":"topic1","payloadType":"default","payload":"","expressionType":"cron","expression":"4 6,9,11,13,15 * * *","location":"","offset":"0","solarType":"all","solarEvents":"sunrise,sunset"}],"x":160,"y":2820,"wires":[["ee1f3162147de5e3"]]},{"id":"690ba182ca09fbb8","type":"influxdb batch","z":"5ad55a7b0d4580eb","influxdb":"64647facf05f4c10","precision":"","retentionPolicy":"","name":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"mwdebian","bucket":"Node-Red Test","x":900,"y":2820,"wires":[]},{"id":"8c21582d4d69d761","type":"debug","z":"5ad55a7b0d4580eb","name":"result","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":610,"y":2740,"wires":[]},{"id":"64647facf05f4c10","type":"influxdb","hostname":"127.0.0.1","port":"8086","protocol":"http","database":"database","name":"MWDebian InfluxDB","usetls":false,"tls":"","influxdbVersion":"2.0","url":"http://mwdebian.fritz.box:8086","rejectUnauthorized":false}]

The JSON Node can be deleted as you can specify in the Http-request that you want to have a JSON object.

I used the batch Node - but I guess you can use the normal out Node as well with a different syntax

Do I guess you have to use the batch node when you want to specify other timestamps as the current timestamp. This is what I found out after reading the description:

1 Like

I will modify your data for the batch node as well. Just a moment

fantastic. you are my hero.
thanks a lot.

I modified the Change Node ( "Prognose_W":$[1]*1000,) , because the power ist coming in kW.

Ok - then I will not further modify your data. I modified it according your input:

https://try.jsonata.org/WmHvAIo0N

[{"id":"f61cd9f90ad07d79","type":"inject","z":"5ad55a7b0d4580eb","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"1733727600\":[1733727600,0,0],\"1733731200\":[1733731200,0.324,0.324],\"1733734800\":[1733734800,0.95,1.274],\"1733738400\":[1733738400,0.852,2.126],\"1733742000\":[1733742000,1.532,3.658],\"1733745600\":[1733745600,2.077,5.735],\"1733749200\":[1733749200,0.5,6.235],\"1733752800\":[1733752800,0.302,6.537],\"1733756400\":[1733756400,0,6.537],\"1733814000\":[1733814000,0,0],\"1733817600\":[1733817600,0.324,0.324],\"1733821200\":[1733821200,0.565,0.889],\"1733824800\":[1733824800,0.648,1.537],\"1733828400\":[1733828400,0.675,2.212],\"1733832000\":[1733832000,0.619,2.831],\"1733835600\":[1733835600,0.502,3.333],\"1733839200\":[1733839200,0.303,3.636],\"1733842800\":[1733842800,0,3.636]}","payloadType":"json","x":330,"y":3060,"wires":[["975e056305da0b28"]]},{"id":"975e056305da0b28","type":"change","z":"5ad55a7b0d4580eb","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$zip(\t   payload.*#$i[$i%3=0],\t   payload.*#$i[$i%3=1],\t   payload.*#$i[$i%3=2]\t).{\t   \"measurement\" : \"Solarprognose 3\",\t   \"timestamp\" : $[0] *1000,\t   \"fields\" : {\t       \"power\" : $[1],\t       \"energy\" : $[2]     \t   } \t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":510,"y":3060,"wires":[["cfbf1ad5c1013f44","af0e0d7934d4a806"]]},{"id":"af0e0d7934d4a806","type":"debug","z":"5ad55a7b0d4580eb","name":"Daten","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":690,"y":3020,"wires":[]},{"id":"cfbf1ad5c1013f44","type":"influxdb batch","z":"5ad55a7b0d4580eb","influxdb":"64647facf05f4c10","precision":"","retentionPolicy":"","name":"","database":"database","precisionV18FluxV20":"ms","retentionPolicyV18Flux":"","org":"mwdebian","bucket":"Node-Red Test","x":760,"y":3060,"wires":[]},{"id":"64647facf05f4c10","type":"influxdb","hostname":"127.0.0.1","port":"8086","protocol":"http","database":"database","name":"MWDebian InfluxDB","usetls":false,"tls":"","influxdbVersion":"2.0","url":"http://mwdebian.fritz.box:8086","rejectUnauthorized":false}]

Danke. Ist nicht nötig. Funktioniert wunderbar.
Da hatte ich nur schon den Datenbereich rausgezogen.