Can any one help with a more elegant way of me extracting the data I need from a JSON string

General
1 
[{"cid":"PWER","data":[{"1697909589000":14934}],"sid":"663404","units":"W","age":1},{"cid":"PWER_GAC","data":[{"[{"cid":"PWER","data":[{"1697909589000":14934}],"sid":"663404","units":"W","age":1},{"cid":"PWER_GAC","data":[{"1697909587000":4161}],"sid":"811455","units":"W","age":3}]":4161}],"sid":"811455","units":"W","age":3}]

Hi, I am looking for a little help in understanding how to build a more elegant way of extracting a specific piece of data where the descriptor field keeps changing.

The string above comes from an API call to my Efergy system and is giving me the power readings from two circuits every 30 seconds.

I am trying in this case to read and extract the number 4161 which is the power in watts but the descriptor is the number "1697909587000" and it changes with every API call (it's the time of the reading).

I have managed to do this using a series of function and split nodes but it feels very clumsy. My very limited understanding of how to extract data from a JSON string means I can't figure out how to get at the specific data I want if I don't know the descriptor name (I hope this makes sense). I feel it should be possible with just a function node and a bit of code. Anyway, I thought I would ask on the forum for ideas.

I do have it working with several nodes but It just feels really clunky.

kind regards

Purclewan.

2

Your JSON seems to be malformed - could you try posting it again please?

1 Like
3 
[
    {
        "cid": "PWER",
        "data": [
            {
                "1698239817000": 949
            }
        ],
        "sid": "663404",
        "units": "W",
        "age": 3
    },
    {
        "cid": "PWER_GAC",
        "data": [
            {
                "1698239819000": 129
            }
        ],
        "sid": "811455",
        "units": "W",
        "age": 1
    }
]
4

This is the data from a few moments ago. I hope it helps. In this case the data I am trying to extract is 129

Purclewan

5

JSONata in a change node would do it
e.g

[{"id":"f63fd13bb5be68da","type":"inject","z":"d1395164b4eec73e","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[     {         \"cid\": \"PWER\",         \"data\": [             {                 \"1698239817000\": 949             }         ],         \"sid\": \"663404\",         \"units\": \"W\",         \"age\": 3     },     {         \"cid\": \"PWER_GAC\",         \"data\": [             {                 \"1698239819000\": 129             }         ],         \"sid\": \"811455\",         \"units\": \"W\",         \"age\": 1     } ]","payloadType":"json","x":130,"y":1120,"wires":[["b7f0a1f0d613a89b"]]},{"id":"b7f0a1f0d613a89b","type":"change","z":"d1395164b4eec73e","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$$.payload[1].data[0].*","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":300,"y":1120,"wires":[["944c1d991e8369fe"]]},{"id":"944c1d991e8369fe","type":"debug","z":"d1395164b4eec73e","name":"debug 344","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":510,"y":1120,"wires":[]}]

Or You could use Object.keys() in a function node to get the property key.

6

Hi @Purclewan

based on that payload, something like this may work

const KV = msg.payload.find((C) => C.cid === 'PWER_GAC').data[0]
let Value;
for (let key in KV) {
  Value = KV[key]
  break;
}

return {payload:Value}
7

This is no-code way of doing it

image
image1352Ă—387 21.2 KB

1 Like
8

That is very like my initial attempt except I wrote msg.payload.data(0). I will try your suggestion and see if it works.

Many thanks.

1 Like
9

Works perfectly. Thank you.

10

Thanks everyone. Appreciate the help.

11

Here is a another (more general purpose) solution, imo... fwiw...

Using a change node, restructure the entire payload object to retain all the data points while also converting the key to a usable timestamp -- the new msg.payload looks like this:

{
  "PWER": {
    "cid": "PWER",
    "data": 949,
    "sid": "663404",
    "units": "W",
    "age": 3,
    "timestamp": "2023-10-25T13:16:57.000Z"
  },
  "PWER_GAC": {
    "cid": "PWER_GAC",
    "data": 129,
    "sid": "811455",
    "units": "W",
    "age": 1,
    "timestamp": "2023-10-25T13:16:59.000Z"
  }
}

I prefer to use this JSONata expression:

payload {
    cid: $ ~> | $ | 
        data {
            "timestamp": $fromMillis($number($keys($)[0])),
            "data": *
        }
    |
}

(although most "normal" people would opt for plain JS ;*)

The big advantage to building one object with multiple properties is that downstream nodes (or dashboard graphs/gages) can refer to each measurement by name (or cid), or using JS function syntax like this example:

let power = payload.PWER.data;
let units = payload.PWER.units;
let dts = new Date(payload.PWER.timestamp); // coerce string to a Date object
if (power > 500) {
    node.log(`High Power reading: ${power} ${units} on ${dts.toLocaleString()}`);
}
2 Likes
12

Thanks for this. Lovely bit of code but beyond my understanding of how it works.

Still, I have found it useful for another project extracting some of those other bits of data you describe.

Thank you.

13

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