Mysql output as input for list node

Dashboard
1

I made a dashboard for learning IR and RF codes and execute them.
Storage is in mysql.

I have 99% working, but the last part wont .

I like to fill a list node based on devices that are 'learned'and already saved in the mysql database.

So i retrieve the unique names from the devices from mysql and try to fill the list node..

I added the flow and added the different content
I see the structure that comes from mysql is not the right structure for the list node...

Expanation content:
orgdevices is the content and structure that works for a list node
sqlnames is the content i get after querying mysql
and payload is the combination between old and new (and does not work)

debug

sqlenlistnodes
sqlenlistnodes804Ă—1040 26.9 KB

flow
flow.txt (3.2 KB)

2

Hi, here is a simple example how to convert the sql array to your list object using a change node and JSONata expression.

[{"id":"43adcb99.aec6a4","type":"inject","z":"c74669a0.6a34f8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[{\"device\":\"new device\"},{\"device\":\"usblamp\"},{\"device\":\"tvhuiskamer\"}]","payloadType":"json","x":80,"y":2780,"wires":[["4fdca0fd.3ee9d","31c9512b.c6d1e6"]]},{"id":"4fdca0fd.3ee9d","type":"change","z":"c74669a0.6a34f8","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"[\t   {\t       \"title\":\"devices\",\"icon\":\"http:www.site.com\",\t       \"menu\":payload.*\t   }\t]","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":280,"y":2800,"wires":[["31c9512b.c6d1e6"]]},{"id":"31c9512b.c6d1e6","type":"debug","z":"c74669a0.6a34f8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":490,"y":2840,"wires":[]}]
3

thanks, gonna try it!
and cool, never used jsonata

4

It works, but i dont understand why it works :slight_smile:

i dont see the construct that does the magic, why is the payload only the values of the payload and not the fields/labels in front of it... what in this contstruc convertthsi, or is this what jsonata does?

5

The magic was payload.* which creates an array form all values in the payload object

JSONata is a language designed to manipulate json

6

thanks !
nice study object for the rainy days....
Must know more about jsonata

1 Like
7

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