Spliting payload

[ACD2040]

Hello,
We have this msg.payload and we need to get the value of avg(IN01)
When we use .split code, we get the error that's not a function.
Can you advice us or give a example on how to work ?
Thanks,

31/07/2023, 11:45:39node: debug 3SELECT avg(IN01) FROM COMPTEUR_GENERAL; : msg.payload : array[1]
array[1]
0: object
avg(IN01): 77.7317

[E1cid]

Your payload is an array of 1 object not a string, .split is a function of a string value.
msg.payload[0]["avg(IN01)"] should hold the value 77.7317
To give the payload property a better name change you sql query to
SELECT avg(IN01) AS average_value FROM COMPTEUR_GENERAL;
then the value would be msg.payload[0].average_value

[meeki007]

Can you share your msg object with us please.

When you paste it here please use formatting for the code.
https://discourse.nodered.org/t/how-to-post-code-or-flows/69516

Also a sharing (export) of your current flow will help as well.

[ACD2040]

Thanks for your rapid answer.

With msg.payload[0]["avg(IN01)"], I Have the same output

array[1]
0: object
avg(IN01): 77.7317

[E1cid]

Try this example

[{"id":"3e9e25c4f6c0d19e","type":"inject","z":"b9860b4b9de8c8da","name":"","props":[{"p":"msg.payload[0][\"avg(IN01)\"]","v":"77.7317","vt":"num"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT avg(IN01) FROM COMPTEUR_GENERAL;","x":230,"y":80,"wires":[["cf43de2fe6b665d1","7082854e73fb5f27"]]},{"id":"cf43de2fe6b665d1","type":"debug","z":"b9860b4b9de8c8da","name":"debug 324","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":310,"y":140,"wires":[]},{"id":"7082854e73fb5f27","type":"change","z":"b9860b4b9de8c8da","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"msg.payload[0][\"avg(IN01)\"]","tot":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":420,"y":80,"wires":[["a96c0f091f51f463"]]},{"id":"a96c0f091f51f463","type":"debug","z":"b9860b4b9de8c8da","name":"debug 325","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":550,"y":120,"wires":[]}]

How to import a flow

These links may help
working with messages
essentials videos

[ACD2040]

You make my day; It's ok.
I need to learn more !

Thanks a lot,
marc,

[ACD2040]

If I can ask a help.

I need also display number of records in DB.

I have this as output but how to get only 126 ?
Thanks,

SELECT COUNT(IN01) FROM COMPTEUR_GENERAL; : msg.payload : array[1]
array[1]
0: object
COUNT(IN01): 126

[jbudd]

You will make life simpler for yourself if you follow @E1cid's advice and change your SQL to use only alphabetic characters in the returned field names.

For example
SELECT COUNT(IN01) AS records FROM COMPTEUR_GENERAL

Then the value you want will be in msg.payload[0].records

[ACD2040]

I tried but I get "undefined" messahe as output

[jbudd]

What database are you using? eg Mariadb, MSSQL, ...

Can you show us the output from the database node?

[ACD2040]

Maria Db on SYnology. Its' ok I found my error.

Thanks,

marc,