Change JSON output (to line protocol or other JSON)

General
1

Hi I have a solar data input that gives me a JSON. I need to convert it to line protocol.

Input:
{"forecasts":[{"pv_estimate":2.986,"pv_estimate10":2.923,"pv_estimate90":2.986,"period_end":"2022-04-17T09:00:00.0000000Z","period":"PT30M"},{"pv_estimate":3.2842,"pv_estimate10":3.1522,"pv_estimate90":3.2842,"period_end":"2022-04-17T09:30:00.0000000Z","period":"PT30M"}, ... ]}

Output:
sunhours,source=solcast pv_estimate=2.986,pv_estimate10=2.923,pv_estimate90=2.986 1650186000000
sunhours,source=solcast pv_estimate=3.2842,pv_estimate10=3.1522,pv_estimate90=3.2842 1650186000000
...

Struggling to get started to load the json into an array. I usually use other tools for this so relatively now to the node-red syntax/nodes.

Also tried to look at JSONATA - if easier changing to a different json format is an option:

{
"measurement": "sunhours",
"tags": {
"source": "solcast"
},
"fields": {
"pv_estimate": 2.986,
"pv_estimate10": 2.923,
"pv_estimate90": "2.986 "
},
"timestamp": 1650186000000
}
...

2

try

[{"id":"edf6c1fde29fbbc9","type":"inject","z":"2b928680670e4e84","name":"Off","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"forecasts\":[{\"pv_estimate\":2.986,\"pv_estimate10\":2.923,\"pv_estimate90\":2.986,\"period_end\":\"2022-04-17T09:00:00.0000000Z\",\"period\":\"PT30M\"},{\"pv_estimate\":3.2842,\"pv_estimate10\":3.1522,\"pv_estimate90\":3.2842,\"period_end\":\"2022-04-17T09:30:00.0000000Z\",\"period\":\"PT30M\"}]}","payloadType":"json","x":270,"y":160,"wires":[["28f7e0e8.d86e98"]]},{"id":"28f7e0e8.d86e98","type":"change","z":"2b928680670e4e84","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$join(\t   $$.payload.forecasts.(\t        \"sunhours,source=solcast\\npv_estimate=\" & $.pv_estimate & \t        \",pv_estimate10=\" & $.pv_estimate10 & \t        \",pv_estimate90=\" & $.pv_estimate90 & \" \" &\t        $toMillis($.period_end)\t   ),\t   \"\\n\"\t)\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":480,"y":160,"wires":[["d042f113ad2a7a3a"]]},{"id":"d042f113ad2a7a3a","type":"debug","z":"2b928680670e4e84","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":670,"y":220,"wires":[]}]

$join(
   $$.payload.forecasts.(
        "sunhours,source=solcast\npv_estimate=" & $.pv_estimate & 
        ",pv_estimate10=" & $.pv_estimate10 & 
        ",pv_estimate90=" & $.pv_estimate90 & " " &
        $toMillis($.period_end)
   ),
   "\n"
)

[edit]
Or if you want an array of objects

$$.payload.forecasts.{
    "measurement": "sunhours",
    "tags": {
        "source": "solcast"
    },
    "fields": {
        "pv_estimate": $.pv_estimate,
        "pv_estimate10": $.pv_estimate10,
        "pv_estimate90": $.pv_estimate90
    },
    "timestamp": $toMillis($.period_end)
}
1 Like
3

If you want to get the two readings separately, you can modify @E1cid's solution and leave out the join

[{"id":"d902ee2.6795d1","type":"inject","z":"33292b7b.b59f84","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"forecasts\":[{\"pv_estimate\":2.986,\"pv_estimate10\":2.923,\"pv_estimate90\":2.986,\"period_end\":\"2022-04-17T09:00:00.0000000Z\",\"period\":\"PT30M\"},{\"pv_estimate\":3.2842,\"pv_estimate10\":3.1522,\"pv_estimate90\":3.2842,\"period_end\":\"2022-04-17T09:30:00.0000000Z\",\"period\":\"PT30M\"}]}","payloadType":"json","x":295.3333435058594,"y":979.0000381469727,"wires":[["26be9975.f46036"]]},{"id":"26be9975.f46036","type":"change","z":"33292b7b.b59f84","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload.forecasts.(\t   \"sunhours,source=solcast\\npv_estimate=\" & $.pv_estimate &          \",pv_estimate10=\" &\t   $.pv_estimate10 &          \",pv_estimate90=\" & $.pv_estimate90 & \" \" &\t   $toMillis($.period_end)    \t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":476.3333740234375,"y":979,"wires":[["1793a4c3.ce3b23"]]},{"id":"1793a4c3.ce3b23","type":"debug","z":"33292b7b.b59f84","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":665.3333435058594,"y":978.0000610351562,"wires":[]}]

The output will look like this:

image
image960Ă—368 72.3 KB

4

Thanks - this works. Good example for me to dissect and learn from!

5

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.