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

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.

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"}"

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

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

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.

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!

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!

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.

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.

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

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.

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;

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\"}"}

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:


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

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.

Hi,

First, thanks again for your help.

I moved the Occupation function:

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

Hi .. it looks ok. i dont have any experience with Telegram. Only one way to find out :wink:

By the way .. why you get a negative number for the "Remaining places" ?
Did you change the test number of 24 to your hall's maximum seat capacity ?

let remainingSeats = 24 - seats;

Thank you very much again.

Regarding with your question, you are right we should not have negative number there. The goal would be to show the occupation of one place as well as the "remaining places". The table is coming from a IP stream camera which is "counting" the number of people in a place with ml in Python, and then the output would be a table with the number of people by zone.

And we would like to send a message to Telegram showing the total number of people and also the remaining spare places to help the people to decide whether they want to go to the shop or not. :slight_smile:

Thanks again for your support. Let´s wait if anyone else could help us with the txt message to be sent through a Telegram sender to a mobile phone.

At the end I found it. I mixed the two functions in only one, and changed the type of "msg.payload" type from "text" to "message".

image

Now we receive the message in two different Telegram chats (private and group):

image

Thanks all,

1 Like

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