How do I extract values from a JSON string?

[amit_meld]

Hello,

I am trying to push data to a data platform from a people counting sensor.
I am receiving below JSON response from the API. I want to push Incount and outcount seperately to the data platform pool. I have set name as dataset name and incount, outcount will be two data streams inside that dataset. Could you please help me in the Function node scripting? The split node splits all below rows seperately in 'payload'.

Channel.0.LineIndex=1
Channel.0.LineIndex.1.Name=Privacybooth
Channel.0.LineIndex.1.InCount=12
Channel.0.LineIndex.1.OutCount=10
Channel.0.LineIndex=2
Channel.0.LineIndex.2.Name=EntryDoor
Channel.0.LineIndex.2.InCount=4
Channel.0.LineIndex.2.OutCount=3

Kind regards,
Amit

[craigcurtin]

Can you show us how you are gettng this into NR ?

I would first try and feed it into the JSON Node and have it output a Javascript object and see what you get

• put a debug node on the output so we can see what it looks like but it will probably be something like

msg.payload.Channel.0.LineIndex etc etc which you can then refernce

Craig

[amit_meld]

I am pulling the data in by using HTTP node and API URL. 'Return a parsed JSON object' is turned ON in HTTP node. I have added a split node to get the payload seperate. I want Incount and outcount values as msg.payload as final upload for PrivacyBooth seperate and EntryDoor seperate.

Please see below the attached image for reference.

Thank your for your quick reply.

Kind regards,
Amit

[craigcurtin]

Nope thats not right - in the dedbug window we should be seeeing actual objects for each incoming message

You want them as a single object if possible because then you can access each of the object properties to do what you want.

Show us a screen grab of the http node and the json node

Change the debug node to complete message object

Craig

[craigcurtin]

WHen you have a javascript object in a message you can reference any field quite easily using a variety of nodes - the change and switch nodes for instance

Here for instance is a weather object my provider returns to me

And i can reference any of its properties like this

Craig

[amit_meld]

This is how it looks like. API gives me a string always.

[amit_meld]

And when I try to change the JSON node Action, debug throws an error saying 'Unexpected token C in JSON at position 0'

I am unable to get the payload in the format you have stated :

I have done another flow in the javascript object format where I have successfully used function node for scripting and push the data to dataset. But with the payload I am receiving, I am not sure how do I retrieve the data from the string separately.

-Amit

[Nodi.Rubrum]

This is where a function node is your friend, where you can get down and dirty with JavaScript to parse or extract values from given payload properties. Which object property is the one in question?

Access the array via a typical subscript, msg.payload[0], msg.payload[1], etc. Then var max=msg.payload[0].air_temperature_maximum; in a functional node, for example.

[amit_meld]

No Matter if I use JSON node or use 'Return Parsed JSON object' in HTTP node. I receive below below message as output in String format-

Here, if I copy the path, it is always 'Payload' and I can not select individual object's path as 'Payload.channel.0.LineIndex.1' . This is where I am struggling.

Do I need to script in such a way that I reach the each character from the string in a loop one by one and extract the values I need? How do I do that? I request you to help me with a sample or guide.

Kind regards,
Amit

[Colin]

That is because it is not a JSON string, it is just a string.

[zenofmud]

If the http request is returning a parsed json object, you don’t need the json node. If you add a debug node (set to display the complete msg object) to the output of the http request node, you will see if it is an object or not.

[craigcurtin]

Ok just so we are all on the same page can you do the following

1. Inject node
2. htt request node with apu etc and return to a parsed JSON object
3. Debug node - set to show complete message object

No other split, function, JSON nodes

And then show us the debug output window please

Craig

[craigcurtin]

@amit_meld

No Matter if I use JSON node or use 'Return Parsed JSON object' in HTTP node. I receive below below message as output in String format-

Here, if I copy the path, it is always 'Payload' and I can not select individual object's path as 'Payload.channel.0.LineIndex.1' . This is where I am struggling.

Do I need to script in such a way that I reach the each character from the string in a loop one by one and extract the values I need? How do I do that? I request you to help me with a sample or guide.

If after you do the test that i listed above can you also do the following so we get a better idea of what that string is - it appears that it is a string with a (NL) new line to delimit each of the values

To make sure this is the case can you install the following node

node-red-contrib-primitives

And then feed the output of the http node into the String SPlit node that it installs

And set that to

And then Run - refresh the context data and show us the flow context

Craig

[E1cid]

As others have said the string is not a JSON string, so you will need to parse the string.

you have not said how you wish the output to look so i have parsed it to an array of objects in my example.
e.g.

[{"id":"f6332b85.359ec8","type":"inject","z":"bf9e1e33.030598","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":110,"y":100,"wires":[["cd12fe0d.b624d8"]]},{"id":"cd12fe0d.b624d8","type":"template","z":"bf9e1e33.030598","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"plain","template":"Channel.0.LineIndex=1\nChannel.0.LineIndex.1.Name=Privacybooth\nChannel.0.LineIndex.1.InCount=12\nChannel.0.LineIndex.1.OutCount=10\nChannel.0.LineIndex=2\nChannel.0.LineIndex.2.Name=EntryDoor\nChannel.0.LineIndex.2.InCount=4\nChannel.0.LineIndex.2.OutCount=3","output":"str","x":270,"y":100,"wires":[["dd838b61.413be","efaa07a0.5dd0e8"]]},{"id":"dd838b61.413be","type":"function","z":"bf9e1e33.030598","name":"","func":"msg.payload = msg.payload.split(\"\\n\");\nmsg.payload = msg.payload.reduce((acc, str) => {\n    let parts = str.split(/\\.|=/);\n    if(parts.length <= 4){\n        acc[parts[3]-1] = {[parts[2]]:Number(parts[3]), [parts[0]]: Number(parts[1])};\n    }else{\n        acc[parts[3]-1][parts[4]] = isNaN(parseFloat(parts[5])) ? parts[5] : parseFloat(parts[5]);\n    }\n    return acc;\n},[])\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":440,"y":100,"wires":[["9efd2c72.cb6e88"]]},{"id":"efaa07a0.5dd0e8","type":"debug","z":"bf9e1e33.030598","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":460,"y":40,"wires":[]},{"id":"9efd2c72.cb6e88","type":"debug","z":"bf9e1e33.030598","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":620,"y":100,"wires":[]}]

msg.payload = msg.payload.split("\n");
msg.payload = msg.payload.reduce((acc, str) => {
    let parts = str.split(/\.|=/);
    if(parts.length <= 4){
        acc[parts[3]-1] = {[parts[2]]:Number(parts[3]), [parts[0]]: Number(parts[1])};
    }else{
        acc[parts[3]-1][parts[4]] = isNaN(parseFloat(parts[5])) ? parts[5] : parseFloat(parts[5]);
    }
    return acc;
},[])
return msg;

output

[
    {
        "LineIndex": 1,
        "Channel": 0,
        "Name": "Privacybooth",
        "InCount": 12,
        "OutCount": 10
    },
    {
        "LineIndex": 2,
        "Channel": 0,
        "Name": "EntryDoor",
        "InCount": 4,
        "OutCount": 3
    }
]

[amit_meld]

Thank you so much everyone for helping me resolve this task ! I was out of station for the weekend and couldn't reply earlier. Yes, my bad, it is just a String and not 'JSON string'. I have accepted E1cid's post as solution, the flow works as expected. I am revisiting the community as I get to learn more from everyone and hopefully in future I could contribute.