Parse JSON object

edwardradical · 15 April 2021 08:22

Hi guys, I'm not sure if this is the right place to ask this question but I have a JSON document like this:

{"Info":{"Battery":0,"ID":1,"Latitudine":"45.47","Longitudine":"9.19"},"Data":[[{"AcX":16688,"AcY":104,"AcZ":2064,"time":1618343910},{"AcX":16680,"AcY":128,"AcZ":2052,"time":1618346910},{"AcX":16676,"AcY":180,"AcZ":1816,"time":1618349910},{"AcX":16616,"AcY":188,"AcZ":1968,"time":1618352910},{"AcX":16592,"AcY":196,"AcZ":1844,"time":1618355910},{"AcX":16712,"AcY":116,"AcZ":2036,"time":1618358910},{"AcX":16596,"AcY":104,"AcZ":2044,"time":1618361910},{"AcX":16672,"AcY":52,"AcZ":1860,"time":1618364910},{"AcX":16736,"AcY":172,"AcZ":2000,"time":1618367910}]]}

that is sent to node-red through HTTP, once received the packet I need to store every value in my Inlfuxdb. Eventually the DB should have 9 columns:
Info | Battery | ID | Latitudine | Longitudine | AcX | AcY | AcZ | time

My problem is: How can I read every single value if I got a nested Object and a nested Array? Is there any function to split my object (I already tried to use the split node but I end up with the first four values and then the array with AcX, AcY, AcZ and time).

Thanks a lot in advance!

Steve-Mcl · 15 April 2021 08:35

Use a change node to ...
1. set msg.info to msg.payload.Info
2. set msg.payload to msg.payload.Data[0]
then link the change node to a split node.
1. each message should now have the data element in msg.payload and the Info property in msg.info
then link the split node to a function node
1. in there, you can combine the items in msg.payload and msg.info into 1 object using a function node with...

var influxData = {
   ...msg.info,
   ...msg.payload
}
msg.payload = influxData;
return msg;

Colin · 15 April 2021 08:40

Is that a JSON string or have you already converted it to a javascript object? If it is a string then you need to first to feed it through a JSON node to convert it to an object.

You should use the influx batch node for this. That can write the whole set in one go, though you will need to restructure it a bit, the easiest way to do that is in a function node. Make sure you convert any field values to numbers before passing to influx, if they are not numbers already. Do the Battery, ID, Lattitude, and Longitude values refer to all the following data? If so then you need to decide whether thay are fields or tags.

edwardradical · 15 April 2021 09:27

Well this is my flow and my output without changing anything, Latitude and Longitude are characters and not numbers

Steve-Mcl · 15 April 2021 09:33

Did you try my suggestion?

It works!

Demo flow...

[{"id":"7a15c50b.023a4c","type":"inject","z":"a86654ec.266648","name":"your data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"Info\":{\"Battery\":0,\"ID\":1,\"Latitudine\":\"45.47\",\"Longitudine\":\"9.19\"},\"Data\":[[{\"AcX\":16688,\"AcY\":104,\"AcZ\":2064,\"time\":1618343910},{\"AcX\":16680,\"AcY\":128,\"AcZ\":2052,\"time\":1618346910},{\"AcX\":16676,\"AcY\":180,\"AcZ\":1816,\"time\":1618349910},{\"AcX\":16616,\"AcY\":188,\"AcZ\":1968,\"time\":1618352910},{\"AcX\":16592,\"AcY\":196,\"AcZ\":1844,\"time\":1618355910},{\"AcX\":16712,\"AcY\":116,\"AcZ\":2036,\"time\":1618358910},{\"AcX\":16596,\"AcY\":104,\"AcZ\":2044,\"time\":1618361910},{\"AcX\":16672,\"AcY\":52,\"AcZ\":1860,\"time\":1618364910},{\"AcX\":16736,\"AcY\":172,\"AcZ\":2000,\"time\":1618367910}]]}","payloadType":"json","x":138,"y":704,"wires":[["dc59b3eb.14ac3"]]},{"id":"dc59b3eb.14ac3","type":"change","z":"a86654ec.266648","name":"set msg.info and msg.payload","rules":[{"t":"set","p":"info","pt":"msg","to":"payload.Info","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"payload.Data[0]","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":352,"y":704,"wires":[["93d921f4.e0493"]]},{"id":"93d921f4.e0493","type":"split","z":"a86654ec.266648","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":130,"y":768,"wires":[["eff51f68.4d8ff"]]},{"id":"eff51f68.4d8ff","type":"function","z":"a86654ec.266648","name":"merge .info and .payload","func":"var influxData = {\n   ...msg.info,\n   ...msg.payload\n}\ninfluxData.Latitudine = Number(influxData.Latitudine);\ninfluxData.Longitudine = Number(influxData.Longitudine);\nmsg.payload = influxData;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":316,"y":768,"wires":[["b6f82563.22a778"]]},{"id":"b6f82563.22a778","type":"debug","z":"a86654ec.266648","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":518,"y":768,"wires":[]}]

edwardradical · 15 April 2021 09:35

I was trying it but I got some errors, I'm checking what I did wrong

edwardradical · 15 April 2021 09:57

Ok it works, probably I didn't set properly the function node! Thanks a lot!
Now after the function node is it possible to store all the objects in one array or object? I need to store them in a temporary file for others get request but I need all the nine object and I cant append them to the file because when I retrieve another set of nine objects I need to overwrite the temporary file

Steve-Mcl · 15 April 2021 10:00

attach a join node

result...

edwardradical · 15 April 2021 10:12

Thanks a lot! Sorry for the noob question but I'm really new here

Colin · 15 April 2021 10:17

I thought you were writing them to influx.

E1cid · 15 April 2021 10:52

Just for info
This is one of the few times using a change node and JSONata is just as fast and cleaner, as there is no need to rejoin split array.

[{"id":"7a15c50b.023a4c","type":"inject","z":"5a245aa1.510164","name":"your data","props":[{"p":"payload"},{"p":"topic","v":"","vt":"date"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"Info\":{\"Battery\":0,\"ID\":1,\"Latitudine\":\"45.47\",\"Longitudine\":\"9.19\"},\"Data\":[[{\"AcX\":16688,\"AcY\":104,\"AcZ\":2064,\"time\":1618343910},{\"AcX\":16680,\"AcY\":128,\"AcZ\":2052,\"time\":1618346910},{\"AcX\":16676,\"AcY\":180,\"AcZ\":1816,\"time\":1618349910},{\"AcX\":16616,\"AcY\":188,\"AcZ\":1968,\"time\":1618352910},{\"AcX\":16592,\"AcY\":196,\"AcZ\":1844,\"time\":1618355910},{\"AcX\":16712,\"AcY\":116,\"AcZ\":2036,\"time\":1618358910},{\"AcX\":16596,\"AcY\":104,\"AcZ\":2044,\"time\":1618361910},{\"AcX\":16672,\"AcY\":52,\"AcZ\":1860,\"time\":1618364910},{\"AcX\":16736,\"AcY\":172,\"AcZ\":2000,\"time\":1618367910}]]}","payloadType":"json","x":230,"y":4540,"wires":[["dc59b3eb.14ac3"]]},{"id":"dc59b3eb.14ac3","type":"change","z":"5a245aa1.510164","name":"set msg.info and msg.payload","rules":[{"t":"set","p":"info","pt":"msg","to":"payload.Info","tot":"msg"},{"t":"set","p":"payload","pt":"msg","to":"payload.Data[0]","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":444,"y":4540,"wires":[["93d921f4.e0493"]]},{"id":"93d921f4.e0493","type":"split","z":"5a245aa1.510164","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":222,"y":4604,"wires":[["eff51f68.4d8ff"]]},{"id":"eff51f68.4d8ff","type":"function","z":"5a245aa1.510164","name":"merge .info and .payload","func":"var influxData = {\n   ...msg.info,\n   ...msg.payload\n}\ninfluxData.Latitudine = Number(influxData.Latitudine);\ninfluxData.Longitudine = Number(influxData.Longitudine);\nmsg.payload = influxData;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":408,"y":4604,"wires":[["b6f82563.22a778","6af1a26b.2258e4"]]},{"id":"b6f82563.22a778","type":"debug","z":"5a245aa1.510164","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":670,"y":4580,"wires":[]},{"id":"6af1a26b.2258e4","type":"join","z":"5a245aa1.510164","name":"","mode":"auto","build":"string","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":"false","timeout":"","count":"","reduceRight":false,"x":520,"y":4640,"wires":[["666008bf.e4439"]]},{"id":"666008bf.e4439","type":"debug","z":"5a245aa1.510164","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"$millis() - topic","statusType":"jsonata","x":720,"y":4640,"wires":[]},{"id":"6e195fb5.65b33","type":"inject","z":"5a245aa1.510164","name":"","props":[{"p":"payload"},{"p":"topic","v":"","vt":"date"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"{\"Info\":{\"Battery\":0,\"ID\":1,\"Latitudine\":\"45.47\",\"Longitudine\":\"9.19\"},\"Data\":[[{\"AcX\":16688,\"AcY\":104,\"AcZ\":2064,\"time\":1618343910},{\"AcX\":16680,\"AcY\":128,\"AcZ\":2052,\"time\":1618346910},{\"AcX\":16676,\"AcY\":180,\"AcZ\":1816,\"time\":1618349910},{\"AcX\":16616,\"AcY\":188,\"AcZ\":1968,\"time\":1618352910},{\"AcX\":16592,\"AcY\":196,\"AcZ\":1844,\"time\":1618355910},{\"AcX\":16712,\"AcY\":116,\"AcZ\":2036,\"time\":1618358910},{\"AcX\":16596,\"AcY\":104,\"AcZ\":2044,\"time\":1618361910},{\"AcX\":16672,\"AcY\":52,\"AcZ\":1860,\"time\":1618364910},{\"AcX\":16736,\"AcY\":172,\"AcZ\":2000,\"time\":1618367910}]]}","payloadType":"json","x":140,"y":4440,"wires":[["517b19ca.010be8"]]},{"id":"517b19ca.010be8","type":"change","z":"5a245aa1.510164","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"($info := $each(payload.Info, function($v, $k){{$k:$number($v)}});\tpayload.Data[0].$merge([$,$info])\t)","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":320,"y":4440,"wires":[["f13da7e6.5a62","823320ad.ae198"]]},{"id":"f13da7e6.5a62","type":"split","z":"5a245aa1.510164","name":"","splt":"\\n","spltType":"str","arraySplt":1,"arraySpltType":"len","stream":false,"addname":"","x":510,"y":4440,"wires":[["809051e9.7d5008"]]},{"id":"823320ad.ae198","type":"debug","z":"5a245aa1.510164","name":"array","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":640,"y":4400,"wires":[]},{"id":"809051e9.7d5008","type":"debug","z":"5a245aa1.510164","name":"split array","active":false,"tosidebar":true,"console":false,"tostatus":true,"complete":"true","targetType":"full","statusVal":"$millis() - topic","statusType":"jsonata","x":710,"y":4440,"wires":[]}]

(
   $info := $each(
       payload.Info,
       function($v, $k){{$k:$number($v)}}
   );
   payload.Data[0].$merge([$,$info])
)

edwardradical · 15 April 2021 11:37

Yes, I'm doing both because I need to store every values in infuxdb but also I need to create a temporary file

Colin · 15 April 2021 12:35

If you need the data later you could read it back from Influx, or if it is in node-red that you need it then save it in context memory.

chrisn-au · 15 April 2021 13:21

@E1cid I really wish I could get my head around JSONata that is pretty cool

system · 29 April 2021 13:21

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

Topic		Replies	Views
Help parsing nested JSON data General	7	6903	22 August 2019
Basic advice needed on parsing JSON General	3	740	14 April 2019
Extract data from a json object General	5	601	26 June 2022
Split Array and Objects General	19	1473	8 October 2022
HELP extract data from object/json General node-red-dashboard , function-node	6	141	5 May 2024

Parse JSON object

Related Topics