How to convert an array to a text - NodeRED read from MySQL to be sent to Telegram

General
1

Hello,

I am trying to send information read from MySQL tables to telegram. For doing that I want to convert an array (or part of the array) to text in order to be able to send it to a Telegram chat.

image
image753×118 8.64 KB

This is the flow the I have. The array that I am reading from MySQL is:

image
image280×585 15.5 KB

And I am trying to send some information from this array to Telegram. The Function to send the message which I am using is:

image

Sorry I am being initiated in this field, and I don´t have experience in coding in JavaScript.

Thank you in advance

2

What part of msg.payload do you want to send?

Also chatId need to be an array if more than one id.
"chatId": ["8345675","-483888"],

3

Hello again,

First thank for your answer.

Now I did a query to deliver a JSON with information which I would like to send as a text to a Telegram message. THis is the JSON:

image

I would like to transform/change it to a text message and send it to a Telegram sender as payload.

Many thanks!

4

Can i have a example i can copy and paste, for testing

5

You can do these things using a JSONata expression in a node-red change node.
So in that case you don't need to learn JavaScript.

If you share an input example (that can be copy pasted - so not a screenshot) and explain clearly the output you want then I am sure someone will soon share the JSONata expression that does the job.

6

Thanks,

This is the flow:

[{"id":"3f299c68.6dfc14","type":"tab","label":"MySQL","disabled":false,"info":""},{"id":"caae444a.4055d8","type":"inject","z":"3f299c68.6dfc14","name":"","topic":"select concat(char(123),char(34),\"Total people\",char(34),char(58),\" \",CONVERT(Zone1+Zone2+Zone3+Zone4+Zone5+Zone6+Zone7+Zone8+Zone9+Zone10+Zone11+Zone12,CHAR),char(44),char(34),\"Remaining people to reach limit\",char(34),char(58),\" \",CONVERT(36-(Zone1+Zone2+Zone3+Zone4+Zone5+Zone6+Zone7+Zone8+Zone9+Zone10+Zone11+Zone12),CHAR),char(44),\" \",char(34),\"Timestamp\",char(34),char(58),\" \",char(34),date_format(now(),\"%d/%m/%Y, %H:%i\"),char(34),char(125))  from tfm40.t2020914 ORDER BY ID DESC LIMIT 1;","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":120,"y":100,"wires":[["7b078dc.6d28d74"]]},{"id":"7b078dc.6d28d74","type":"mysql","z":"3f299c68.6dfc14","mydb":"27b842b4.d3d94e","name":"TFM40","x":280,"y":100,"wires":[["a245e925.41c168"]]},{"id":"a245e925.41c168","type":"debug","z":"3f299c68.6dfc14","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":450,"y":100,"wires":[]},{"id":"27b842b4.d3d94e","type":"MySQLdatabase","z":"","name":"","host":"localhost","port":"3306","db":"TFM40","tz":""}]

The JSON which is being read from MySQL is:

"{"Total people": 34,"Remaining people to reach limit": 2, "Timestamp": "21/09/2020, 07:59"}"

7

Sure, see the code below. Many thanks for your answer!

8

OK you have shared an input example and what should the output be for that input example ?

9

We need an example of the returned payload from the mysql request. You showed a screenshot, but we can not copy info from that. There is are hoover over icon to the right of the object s, one will say copy path the other copy value. Copy value and post here. Then we can advise on how best to convert to the format you need.

10

The output that I would like to have would be the string as a text. Actually it is a string with the format of a JSON.

"Total People: 34, Remaining people to reach limit:2, Timestamp: 21/09/2020, 07:59" or maybe a part of this text.

Thanks!

11

Sorry, now I understand. This is the query that I use to read in My SQL:

[{"concat(char(123),char(34),"Total people",char(34),char(58)," ",CONVERT(Zone1+Zone2+Zone3+Zone4+Zone5+Zone6+Zone7+Zone8+Zone9+Zone10+Zone11+Zone12,CHAR),char(44),char(34),"Remaining people to reach limit",char(34),char(58)," ",CONVERT(36-(Zone1+Zone2+Zone3+":"{"Total people": 34,"Remaining people to reach limit": 2, "Timestamp": "21/09/2020, 19:24"}"}]

Thanks!

12

use a function node and json.stringify()

Can not help more as i know nothing about the json you need converting, it's paths or values.

This is not a valid JSON format
"Total People: 34, Remaining people to reach limit:2, Timestamp: 21/09/2020, 07:59"
It has no { " " }

No we need the values from the object in the debug pane.

13

Sorry, I am just begining. I am reading a string from MySQL. And I would like to convert it to a text. with JavaScript.

This is the value of the array[1] (it zontains only 1 object: a big string)
[{"concat(char(123),char(34),"Total people",char(34),char(58)," ",CONVERT(Zone1+Zone2+Zone3+Zone4+Zone5+Zone6+Zone7+Zone8+Zone9+Zone10+Zone11+Zone12,CHAR),char(44),char(34),"Remaining people to reach limit",char(34),char(58)," ",CONVERT(36-(Zone1+Zone2+Zone3+":"{"Total people": 34,"Remaining people to reach limit": 2, "Timestamp": "21/09/2020, 20:17"}"}]

This is the value of the string:

{"concat(char(123),char(34),"Total people",char(34),char(58)," ",CONVERT(Zone1+Zone2+Zone3+Zone4+Zone5+Zone6+Zone7+Zone8+Zone9+Zone10+Zone11+Zone12,CHAR),char(44),char(34),"Remaining people to reach limit",char(34),char(58)," ",CONVERT(36-(Zone1+Zone2+Zone3+":"{"Total people": 34,"Remaining people to reach limit": 2, "Timestamp": "21/09/2020, 20:17"}"}

And I would like to parse this string to a mesage.txt with a function in JavaScript, byt I don´t know how.

Sorry for my explanations, and thanks for your answers.

14

The string has a format similar to JSON, but not JSON.

15

OK in this image it show an array with 1 object
the key is the purple text and the value the red text.
It looks like it is misformed the key.

To the right of the 0:object there should be some hover over buttons . Click the on that says copy path and post here.

Please post the copied value between triple back ticks eg below
```
the copied value
```
Make sure the back ticks are on thier own lines.

16

Is it just me .. that mySql reply doesnt seem like a 'healthy' sql query result :wink:
What are you trying to accomplish with your select sql query and how does your data really look like in the db ?

did you try the JSON.stringify() command on the db result as shown in your first images .. as suggested by @E1cid ?

I mean .. in a function node after your database use

msg.payload = JSON.stringify(msg.payload, null, 1)
return msg;

to format the array of objects into a string and in your initial sql query do a simple
select * from tfm40.t2020914 ORDER BY ID DESC LIMIT 1

any additional calculations .. like calculating the seats of the hall or remaining seats, i think it will be cleaner to do in javascript in a function.

For example (if you use the above sql query that returns the most recent single record and IF the zones results are always the same)

let x = msg.payload[0];
let seats = x.Zone1 + x.Zone2 + x.Zone3 + x.Zone4 + x.Zone5 + x.Zone6 + x.Zone7 + x.Zone8 + x.Zone9 + x.Zone10 + x.Zone11 + x.Zone12
let remainingSeats = 24 - seats; 

msg.payload = "Total Seats : " + seats + " Remaining Seats : " + remainingSeats ;
return msg;
17

Thanks again for your help. This is the result of the array[1] copied path:

payload

This is the array[1] copied value:

[{"concat(char(123),char(34),\"Total people\",char(34),char(58),\" \",CONVERT(Zone1+Zone2+Zone3+Zone4+Zone5+Zone6+Zone7+Zone8+Zone9+Zone10+Zone11+Zone12,CHAR),char(44),char(34),\"Remaining people to reach limit\",char(34),char(58),\" \",CONVERT(36-(Zone1+Zone2+Zone3+":"{\"Total people\": 34,\"Remaining people to reach limit\": 2, \"Timestamp\": \"22/09/2020, 06:50\"}"}]

Now the 0: object copied path

payload[0]

And the 0: object copied value:

{"concat(char(123),char(34),\"Total people\",char(34),char(58),\" \",CONVERT(Zone1+Zone2+Zone3+Zone4+Zone5+Zone6+Zone7+Zone8+Zone9+Zone10+Zone11+Zone12,CHAR),char(44),char(34),\"Remaining people to reach limit\",char(34),char(58),\" \",CONVERT(36-(Zone1+Zone2+Zone3+":"{\"Total people\": 34,\"Remaining people to reach limit\": 2, \"Timestamp\": \"22/09/2020, 06:50\"}"}
18

First, many thanks, it is exactly how you interpretated it.

Now I inserted two nodes (1 JSON stringify + 1 for occupation calculation), with the initial query:

select * from tfm40.t2020914 ORDER BY ID DESC LIMIT 1

But I think that I have to convert the string to numbers because it does not recognise the numbers:

image
image1206×273 28.7 KB

First function node´s output (actually it is a string[253]):

[ {  "id": 2,  "Timestamp": "2020-09-14T17:02:22.000Z",  "Camera": "hall",  "Zone1": 0,  "Zone2": 3,  "Zone3": 0,  "Zone4": 5,  "Zone5": 5,  "Zone6": 4,  "Zone7": 4,  "Zone8": 2,  "Zone9": 4,  "Zone10": 5,  "Zone11": 2,  "Zone12": 0 }]

Second function node`s output (string[47]):

Current Occupation : NaN Remaining Places : NaN

How could I convert the strings to floats (only the part of the numbers)? Shouldn´t I need to do it?

Thanks

19

Hi Cesar,

The second function "Occupation" cannot be used after the "JSON stringify" as it will not have access to the same db result. It was only meant as an example and not to be used chained like you did in your screenshot above. Move the Occupation function directly after your db and see if it works there.

As previous posters asked .. you have to tell us how you want your final message to look like.
Write it by hand how you imagine it to be.

20

Hi,

First, thanks again for your help.

I moved the Occupation function:

image
image1689×288 33.8 KB

From the occupation node we have this output (it is ok like this, we could adapt it at the end).

Current Occupation : 34 Remaining Places : -10

From the second function (to prepare the message for Telegram sender (as text payload I guess).

{"content":"Current Occupation : 34 Remaining Places : -10","chatId":["635874737","-418602743"],"type":"text"}

I think that I should convert it now to be able to send it as a message to a Telegram bot. Is it correct?

Many thanks again,

César