# Mysql output as input for list node

**URL:** <https://discourse.nodered.org/t/mysql-output-as-input-for-list-node/46934>\
**Category:** Dashboard\
**Created:** [9 June 2021 17:24 UTC](https://discourse.nodered.org/t/mysql-output-as-input-for-list-node/46934 "2021-06-09T17:24:40Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![pvklink](https://avatars.discourse-cdn.com/v4/letter/p/46a35a/32.png) [@pvklink](https://discourse.nodered.org/u/pvklink)\
**Post date:** [9 June 2021 17:24 UTC](https://discourse.nodered.org/t/mysql-output-as-input-for-list-node/46934/1 "2021-06-09T17:24:40Z")

</div>

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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/6/66fa932b43c7c7300249cbca4a1fd261243f3ce7.jpeg)

flow  
[flow.txt](https://discourse.nodered.org/uploads/short-url/oODhLTyNVSe5OEFwiyDodamvcYw.txt) (3.2 KB)

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [9 June 2021 18:51 UTC](https://discourse.nodered.org/t/mysql-output-as-input-for-list-node/46934/2 "2021-06-09T18:51:17Z")

</div>

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

```auto
[{"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":[]}]

```

---

<div class="post-metadata">

**Author:** ![pvklink](https://avatars.discourse-cdn.com/v4/letter/p/46a35a/32.png) [@pvklink](https://discourse.nodered.org/u/pvklink)\
**Post date:** [9 June 2021 19:10 UTC](https://discourse.nodered.org/t/mysql-output-as-input-for-list-node/46934/3 "2021-06-09T19:10:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![pvklink](https://avatars.discourse-cdn.com/v4/letter/p/46a35a/32.png) [@pvklink](https://discourse.nodered.org/u/pvklink)\
**Post date:** [9 June 2021 19:20 UTC](https://discourse.nodered.org/t/mysql-output-as-input-for-list-node/46934/4 "2021-06-09T19:20:04Z")

</div>

It works, but i dont understand why it works 🙂

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?

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [9 June 2021 19:22 UTC](https://discourse.nodered.org/t/mysql-output-as-input-for-list-node/46934/5 "2021-06-09T19:22:58Z")

</div>

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

> **[JSONata Documentation · JSONata](https://docs.jsonata.org/overview)**
>
> \## Introduction

JSONata is a language designed to manipulate json

---

<div class="post-metadata">

**Author:** ![pvklink](https://avatars.discourse-cdn.com/v4/letter/p/46a35a/32.png) [@pvklink](https://discourse.nodered.org/u/pvklink)\
**Post date:** [9 June 2021 21:15 UTC](https://discourse.nodered.org/t/mysql-output-as-input-for-list-node/46934/6 "2021-06-09T21:15:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [23 June 2021 21:16 UTC](https://discourse.nodered.org/t/mysql-output-as-input-for-list-node/46934/7 "2021-06-23T21:16:02Z")

</div>

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