Extract multiple variables from a JSON object



Please could someone help me to extract a number of variable values from a JSON object returned by my home automation controller - please see screen shot attached

To start with I’m looking to extract the values you can see opened, I’ve tried a functions with variations of msg.payload[0][0][0] but I only get errors. A kick in the right direction would be appreciated (many thanks)

Extract of JSON below.

{ "Device_Num_22": { "states": [ { "id": 167, "service": "urn:upnp-org:serviceId:SwitchPower1", "variable": "Status", "value": "0" }, { "id": 168, "service": "urn:upnp-org:serviceId:Dimming1", "variable": "LoadLevelStatus", "value": "0" }, { "id": 169, "service": "urn:upnp-org:serviceId:Dimming1", "variable": "LoadLevelTarget", "value": "0" }, { "id": 170, "service": "urn:micasaverde-com:serviceId:HaDevice1", "variable": "Configured", "value": "1" }, { "id": 171, "service": "urn:micasaverde-com:serviceId:HaDevice1", "variable": "LastUpdate", "value": "1455968462" }, { "id": 172, "service": "urn:micasaverde-com:serviceId:HaDevice1", "variable": "FirstConfigured", "value": "1341655010" }, { "id": 173, "service": "urn:micasaverde-com:serviceId:HaDevice1", "variable": "CommFailure", "value": "1" }, { "id": 174, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "Capabilities", "value": "211,156,0,4,17,1,L,R,B,RS,|38:1,39,112,114,115,133,134,142," }, { "id": 175, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "ManufacturerInfo", "value": "271,256,262" }, { "id": 176, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "VersionInfo", "value": "3,3,34,1,6" }, { "id": 177, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "NodeInfo", "value": "26,27,70,72,73,85,86,8e," }, { "id": 178, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "ConfiguredAssoc", "value": "" }, { "id": 179, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "PollSettings", "value": "60" }, { "id": 180, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "Neighbors", "value": "1,10,37,41," }, { "id": 181, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "LastReset", "value": "1455968460" }, { "id": 182, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "AssociationNum", "value": "3" }, { "id": 183, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "PollOk", "value": "9" }, { "id": 184, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "LastRouteUpdate", "value": "1538138819" }, { "id": 185, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "VariablesSet", "value": "14,1d,1" }, { "id": 186, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "VariablesGet", "value": "14,1," }, { "id": 187, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "SensorMlType", "value": "" }, { "id": 188, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "SensorMlScale", "value": "" }, { "id": 189, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "NeighborsInverse", "value": "" }, { "id": 190, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "Health", "value": "" }, { "id": 191, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "AutoRoute", "value": "0-5x,10-15,26-15,20-16" }, { "id": 192, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "AllRoutesFailed", "value": "0" }, { "id": 193, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "MultiChEndpoint", "value": "" }, { "id": 194, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "MultiChCapabilities", "value": "" }, { "id": 195, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "ConfiguredName", "value": "" }, { "id": 196, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "MeterType", "value": "" }, { "id": 197, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "MeterScale", "value": "" }, { "id": 198, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "SetPointInfo", "value": "" }, { "id": 199, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "PollTxFail", "value": "" }, { "id": 200, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "HealthDate", "value": "1443490454" }, { "id": 201, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "ConfiguredVariable", "value": "14,1d,1" }, { "id": 202, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "PollNoReply", "value": "15" }, { "id": 203, "service": "urn:micasaverde-com:serviceId:ZWaveDevice1", "variable": "LastWakeup", "value": "1491467301" }, { "id": 204, "service": "urn:upnp-org:serviceId:TemperatureSetpoint1_Heat", "variable": "CurrentSetpoint", "value": "21" } ], "Jobs": [ ], "tooltip": { "display": 0 }, "status": -1 }, "LoadTime": 1537623966, "DataVersion": 624293835, "UserData_DataVersion": 623966640, "TimeStamp": 1538157549, "ZWaveStatus": 1, "LocalTime": "2018-09-28 18:59:09 N" }

I’m working on an idea that I could extract specific variables from calls made to my HA controler and then use them in some text to speech flows i’m working on too.



The docs have a guide on understanding the structure of a message: https://nodered.org/docs/user-guide/messages#understanding-the-structure-of-a-message

In particular, it explains how the debug sidebar can be used to get the full path to any message element copied straight to your clipboard.




Thanks Nick

I recall looking at this now,

One point of feedback, on the above feature is that these buttons do not seem to work on my iPad with Safari, I’ve tried numerous times but it would not appear in the clipboard.

Ok, the above works for one value, but I missed a key point in my question above, in that I will be calling different devices, so the example above is device 22, is there a wild card that can be used for the first part, so I can run different JSON calls via the same function ?



if you google “javascript wildcard” you’ll find loads of tutorials that will help you



What do you get as output if you inject your msg.payload to this change node ?


[{"id":"c28f4e65.7b03f","type":"tab","label":"Flow 7","disabled":false,"info":""},{"id":"78dc82cc.d458bc","type":"change","z":"c28f4e65.7b03f","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"**.states[18].value","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":340,"y":160,"wires":[[]]}]


Unfortunately, Safari is a long way behind current standards I think. Also security is different on mobile devices.



Many thanks @Andrei, that looks good and seems to do the trick nicely :grin:.

I had looked up various things online about how to do javascript wildcards; but as I’m not a programmer - it talked about regex and other things, which I struggle with to apply - I had hoped it would be as simple as an asterix ‘ * ‘ - If I see working examples of how things are done in the area I’m looking to use them - I find I work out and build on things best that way ..

Thanks again



Taking it one step further, I had hoped this would work so I could start dissecting the JSON into individual values I could then work with .. is there a way to do this within a single node ? (I looked at the split node but that does not look like it will help.)

Or is the only way via separate Change nodes?



Perhaps it is possible to build another jsonata expression (a little bit more complex). I will give it a try.



Well, just changing the expression as below may achieve both goals: a) use only one node and b) extract the values...


However I am not sure if this will help you at the end of the day. The above will produce an array with the extracted values but you will not know to which Device_Num and state the values are associated.

As a matter of fact there are other possible solutions but the best one will ultimately depend on how you will process the information downstream. Also I am not sure that you will always want to extract the values from status[18] and status[19]. If you change this requirement you will need to come up with a new jsonata expression. Perhaps if you restate your requirements in a broader way (by saying all that you may ever need to extract and how you will process it later on) then we may realize how to transform the original dataset to easy the solution.



Thanks again for your help on this @Andrei

What I’m trying to do is create a (human understandable) device report / health message, one that can either be sent out via email, or a notification (Prowl) or spoken (via text to speech).

So on demand I can then request (or schedule) a specific device name/number to be called - and the flow would then extract the required variable values to populate key parts of the health message.