Referrencing msg.payload in JSON

liambroughton500 · 21 June 2018 14:45

Hi all,

I have been trying to send text messages through Node-RED, I know there are numerous ways to do this, but the way I have settled on at the moment is using Click Send. I have followed the example on the tutorial, and with a few tweaks to the original code, I have it working.

However, I am now looking to reference my msg.payload data in the JSON code. So here is my JSON code so far:

    {
    "messages": [
        {
            "to": "Phone Number Here",
            "body": "Test Test",
            "from": "Room Service"
        }
    ]
}

So I am now looking to replace the “to”: “Phone Number Here” part of the code, with my msg.payload data that is entered in my ui_form.

I was wondering if anyone has had experience with referencing the msg.payload data in JSON, I have tried doing this with previous ways I have been told (not in JSON though) but I have been unsuccessful. The way I have tried is like so:

{{{#payload}}}
“to”: “{{number}}”
{{{/payload}}}

Thanks,

knolleary · 21 June 2018 15:16

A slightly pedantic point, but its worth making - JSON is a String representation of an Object. You either have a JSON String or a JavaScript Object. It is the object form with which you can start referencing the individual elements of the object.

You don’t say so explicitly, but I assume the number you want is in msg.payload.number?

To create the object you want, you could use a Function node:

msg.payload = {
   "messages": [
      { 
         "to": msg.payload.number,
         "body": "Test Test",
         "from": "Room Service"
        }
    ]
}
return msg;

You could use a template node, set the template to:

{
    "messages": [
        {
            "to": {{payload.number}},
            "body": "Test Test",
            "from": "Room Service"
        }
    ]
}

You could use a Change node configured to set msg.payload to the following expression:

{
    "messages": [
        {
            "to": $.payload.number,
            "body": "Test Test",
            "from": "Room Service"
        }
    ]
}

liambroughton500 · 22 June 2018 09:17

Thanks for that @knolleary

liambroughton500 · 22 June 2018 09:57

@knolleary - I have been trying to reference multiple pieces of data now in the same function node like so:

    {
    "messages": [
        {
            "to": "{{number}}",
            "body": "Room Number - {{Room}} Email - {{Email}} Password - {{Password}}",
            "from": "Room Details"
        }
    ]
}

But now, this is the output I get:

{"to":"123456789","body":"Room Number - Email - Password -","from":"Room Details"}

So it is completely ignoring my next few payload references.

Is there anything else I need to add to my code in order to be able to reference the others?

Thanks.

knolleary · 22 June 2018 10:18

Hi @liambroughton500

You say ‘Function node’ but then you show something using the {{ }} style syntax which is for the Template node.

If you are using the Template node, then compare what you have:

   "to": "{{number}}",

with what I provided in my reply:

   "to": "{{payload.number}}",

Can you see the difference?

liambroughton500 · 22 June 2018 10:26

Hi Nick,

Sorry, it’s been an early one for me today! I mean’t to say in my in my template node.

I tried with the data you said, and when changing the:
"to": "{{number}}",
to
"to": "{{payload.number}}",
it is now not showing the number in the output.

But when I use just {{number}} it does output what I want.

I also tried changing the other payload references to {{payload.Email}} etc, and this didn’t change anything. Still no output on those.

Thanks.

knolleary · 22 June 2018 10:28

In which case you need to share exactly what the message you are passing this template looks like. My responses were based on the assumption it was all under msg.payload - but maybe not. Without that detail, I cannot give you a precise answer.

liambroughton500 · 22 June 2018 10:38

Here is my flow for sending room details. I have an email node set up, and a http request node which links to my Click Send account API.

[{"id":"be9ac30e.6056c","type":"ui_form","z":"e5a2b317.822f2","name":"","label":"","group":"5abf0d7c.3d62b4","order":0,"width":0,"height":0,"options":[{"label":"Please enter the room number to which you want the details for.","value":"Room","type":"text","required":true},{"label":"Please enter the customer's phone number to send their details via text.","value":"number","type":"text","required":false},{"label":"Please enter customer email.","value":"Cust","type":"text","required":false}],"formValue":{"Room":"","number":"","Cust":""},"payload":"","topic":"","x":90,"y":40,"wires":[["2820deb7.048eb2"]]},{"id":"46191f3b.d1f8f","type":"function","z":"e5a2b317.822f2","name":"TEST","func":"msg.topic = \"SELECT * FROM dbo.PasswordStore WHERE Room='\" + msg.payload[\"Room\"] + \"'\";\nreturn msg;","outputs":1,"noerr":0,"x":270,"y":140,"wires":[["1721dee4.a7dad1","561d1965.c6c9e8"]]},{"id":"1721dee4.a7dad1","type":"debug","z":"e5a2b317.822f2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":330,"y":240,"wires":[]},{"id":"253e0d99.caa072","type":"template","z":"e5a2b317.822f2","name":"HTML","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"<!DOCTYPE html>\n<html>\n    <head>\n{{#payload}}\n    <h1>Room Number - {{{Room}}}</h1>\n    </head>\n<body>\n    <p>Email Address - {{{Email}}}</p>\n    <p>Password - {{{Password}}}</p>\n    </body>\n{{/payload}}\n</html>","output":"str","x":710,"y":120,"wires":[["9af45029.d04dc"]]},{"id":"561d1965.c6c9e8","type":"sqldbs","z":"e5a2b317.822f2","mydb":"32e83433.d136cc","querytype":"select","name":"","x":450,"y":180,"wires":[["549d69b5.6aca98"]]},{"id":"549d69b5.6aca98","type":"change","z":"e5a2b317.822f2","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload[0]","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":560,"y":260,"wires":[["253e0d99.caa072","5309eedc.1c17a","e1e3358f.8131f8"]]},{"id":"ec28c11.655804","type":"e-mail","z":"e5a2b317.822f2","server":"smtp.gmail.com","port":"465","secure":true,"name":"","dname":"","x":949,"y":46,"wires":[]},{"id":"9af45029.d04dc","type":"change","z":"e5a2b317.822f2","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"Your Room Details","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":760,"y":80,"wires":[["ec28c11.655804","5a857164.793cd"]]},{"id":"5a857164.793cd","type":"debug","z":"e5a2b317.822f2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":940,"y":160,"wires":[]},{"id":"2820deb7.048eb2","type":"change","z":"e5a2b317.822f2","name":"","rules":[{"t":"move","p":"payload.Cust","pt":"msg","to":"to","tot":"msg"},{"t":"move","p":"payload.number","pt":"msg","to":"number","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":220,"y":80,"wires":[["46191f3b.d1f8f","f4f3de50.62ac9"]]},{"id":"f4f3de50.62ac9","type":"debug","z":"e5a2b317.822f2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":460,"y":80,"wires":[]},{"id":"afcc41c0.28eec","type":"http request","z":"e5a2b317.822f2","name":"Send SMS","method":"POST","ret":"obj","url":"https://rest.clicksend.com/v3/sms/send","tls":"","x":986,"y":460,"wires":[["37246a0f.f8f786"]]},{"id":"238a85c2.a9a68a","type":"debug","z":"e5a2b317.822f2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":996,"y":359,"wires":[]},{"id":"37246a0f.f8f786","type":"debug","z":"e5a2b317.822f2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":1145,"y":434,"wires":[]},{"id":"5309eedc.1c17a","type":"debug","z":"e5a2b317.822f2","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":750,"y":262,"wires":[]},{"id":"e1e3358f.8131f8","type":"template","z":"e5a2b317.822f2","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"{\n    \"messages\": [\n        {\n            \"to\": \"{{payload.number}}\",\n            \"body\": \"Room Number - {{payload.Room}} Email - {{payload.Email}} Password - {{payload.Password}}\",\n            \"from\": \"Room Details\"\n        }\n    ]\n}","output":"json","x":764,"y":369,"wires":[["238a85c2.a9a68a","afcc41c0.28eec"]]},{"id":"5abf0d7c.3d62b4","type":"ui_group","z":"","name":"Print Room Details","tab":"e1207017.0cfd8","disp":true,"width":"12","collapse":false},{"id":"32e83433.d136cc","type":"sqldbsdatabase","z":"","host":"den1.mssql4.gear.host","port":"1433","db":"passwordstore1","dialect":"mssql"},{"id":"e1207017.0cfd8","type":"ui_tab","z":"","name":"Print Room Details","icon":"dashboard"}]

You can see that I have requested the data from my MSSQL Database, and then set the msg.payload of that data to msg.payload[0]. So my data is now under msg.payload[0].

However, I have tried referencing my data as {{msg.payload[0].Room}}, and {{payload[0].Room}}, and both of those come out with the same output as before.

If you need any more info on my flow, please ask and I will do my best to answer.

Thanks.

knolleary · 22 June 2018 10:41

Sharing your flow doesn’t help here. We just need to know the structure of the message you are passing to the Template node.

Wire a Debug node next to your Template node so it receives the same message you are passing it. Configure that node to show the full message, not just msg.payload.
When you get the message in the Debug sidebar, copy it (hover over and click the clipboard button that appears on the right) and share it here.

liambroughton500 · 22 June 2018 10:45

{"payload":{"messages":[{"to":"","body":"Room Number -  Email -  Password - ","from":"Room Details"}]},"topic":"SELECT * FROM dbo.PasswordStore WHERE Room='123'","socketid":"XIaTCaKZvQmYNcMQAAAP","_msgid":"2d7860ec.8178b","to":"","number":"123456789"}

That is the output from the template node, coming from my debug node with the following data in my tempate node:

{
"messages": [
    {
        "to": "{{payload.number}}",
        "body": "Room Number - {{payload.Room}} Email - {{payload.Email}} Password - {{payload.Password}}",
        "from": "Room Details"
    }
]
}

Thanks.

knolleary · 22 June 2018 10:46

Sorry, showing me the output of the Template node does not help.

We need to see the message being passed in to the Template node.

liambroughton500 · 22 June 2018 10:50

Then I believe this is the data you are wanting to see? This is coming from my change node, and then into my template node.

{"payload":[{"Room":123,"Password":"Test                                                                                                                                                                                                                                                            ","Email":"email@email.com                                                                                                                                                                                                                                                 "}],"topic":"SELECT * FROM dbo.PasswordStore WHERE Room='123'","socketid":"XIaTCaKZvQmYNcMQAAAP","_msgid":"8c3c7cfd.264da","to":"","number":"123456789"}

Thanks.

ukmoose · 22 June 2018 10:58

Are you sure thats the full message?

liambroughton500 · 22 June 2018 11:08

I attached a debug node to my change node, and changed the debug node to output the complete msg object.

I then clicked the clipboard button and pasted that in here. So I thought that would be the full message.

However, from your response I am guessing it isn't.

Here is a printscreen of the debug node output in the debug tab, hopefully this helps?

debug

liambroughton500 · 22 June 2018 11:18

Also, if it helps. I am referencing my msg.payload data in my HTML code in a template node (when passing it to an email node) like so:

    <!DOCTYPE html>
<html>
    <head>
{{#payload}}
    <h1>Room Number - {{{Room}}}</h1>
    </head>
<body>
    <p>Email Address - {{{Email}}}</p>
    <p>Password - {{{Password}}}</p>
    </body>
{{/payload}}
</html>

Thanks.

ukmoose · 22 June 2018 11:34

My phone gave up scrolling before I got to anything else. Strangely your screen dump has far fewer spaces in the Password and Email fields.

But from the screenshot it shows that the payload is an array that contains an object.
But if you hover your cursor over the the 123 in the debug window the tooltips should appear that will allow you to copy the path to it.
see https://nodered.org/docs/user-guide/messages

liambroughton500 · 22 June 2018 11:47

@ukmoose - I have tried copying the path and referencing this in my template node… However this still does not work.

The path it is giving me is: payload[0].Room and so forth…

I really am unsure on what to try next… I have just tried referencing the msg.payload[0].Room in the template node (nothing else) and it isn’t showing the data being passed through.

I have tried the following methods:

Thanks.

knolleary · 22 June 2018 11:51

That shows your message has msg.number set - which is why using just {{number}} worked in your Template.

It also shows that msg.payload is an array containing a single element which is the object with the Room/Password/Email properties. And it shows that number doesn’t exist in that object. These are all the sorts of small details that are vital to be able to answer this sort of question - and why I asked you to confirm whether the number you wanted was msg.payload.number back in my original reply.

As it is an element in the array, you can reference those in the Template with {{payload.0.Room}} etc (the mustache template format doesn’t do [0] style array references…)

{
    "messages": [
        {
            "to": "{{number}}",
            "body": "Room Number - {{payload.0.Room}} Email - {{payload.0.Email}} Password - {{payload.0.Password}}",
            "from": "Room Details"
        }
    ]
}

Oh, and are you sure you want to include their password in this message? That isn’t very secure.

Nick

liambroughton500 · 22 June 2018 12:03

@knolleary @ukmoose

Thanks for your help with this guys. So many things to remember haha! That last code you gave me there worked fine Nick!

I am starting to understand all of this a bit more clearly now though. I have actually been able to sort out other parts of my flow that I have been having issues with, without having to ask for advice!

Thanks for the tutoring with this though, it really is appreciated!

Also, regarding the password being sent in the text. Yes I do need this in the text/email. Basically what I have been asked to create is a Room Service system for Hotels.

The idea is, the customer comes in to check into the hotel. The receptionist gives them their room details via email and/or text message. (I may even try and sort out a serial printer to get a physical copy of this, I have tried this in the past but due to not having the suitable hardware I could not get this working properly). The hotel rooms will have a tablet/device in each room with the Node-RED ui on the screen, with the Insert Data Form on it. I am going to have a separate system with the “back end” part of the system, such as updating room details etc…

All of the room details are used for verification on the Room Service form I have setup, so if you enter incorrect room details when submitting a Room Service request, an error message will pop-up stating that the details have been entered incorrectly.

There is quite a lot to the system I have made so far, and that is why I have learnt so so much with it all! And it still isn’t there yet! Not too long to go I would hope though.

It is all very interesting!

ioclaudio · 9 December 2018 11:17

Hi,
I'd like to reference, in a change node, themsg.topic field.

I've tried: $.topic and $.msg.topic, as I can deduce from your post.
But in both case deploying the flow I have the error: ""Invalid 'to' JSON property".
Which is the right syntax?
I can't find a definitive guide to syntax we can use into the nodes.

Thank you very much

Claudio

Topic		Replies	Views
Json, how can i add the msg.payload object as a variable in JSON General	6	3345	17 April 2020
How to Add element to msg.payload FAQs	7	20239	16 August 2021
Super Noob Question Concerning Referencing msg.payload in the next node General	8	390	17 February 2021
Converting msg.payload array into an object to access its value General	4	1087	12 October 2022
Parsing Payload General function-node	15	643	17 February 2023

Referrencing msg.payload in JSON

Related topics