# SQL array to dropdown just lists the labels

**URL:** <https://discourse.nodered.org/t/sql-array-to-dropdown-just-lists-the-labels/93462>\
**Category:** General\
**Created:** [25 November 2024 21:56 UTC](https://discourse.nodered.org/t/sql-array-to-dropdown-just-lists-the-labels/93462 "2024-11-25T21:56:09Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![rbmgf7](https://avatars.discourse-cdn.com/v4/letter/r/c57346/32.png) [@rbmgf7](https://discourse.nodered.org/u/rbmgf7)\
**Post date:** [25 November 2024 21:56 UTC](https://discourse.nodered.org/t/sql-array-to-dropdown-just-lists-the-labels/93462/1 "2024-11-25T21:56:10Z")

</div>

![Screenshot 2024-11-25 154913](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/0/40614f5f4fed687dcd589226121cdf5af591577a.png)

I can't figure out how to get the dropdown to list the values as the options. It only lists the labels so "PumpType" is listed 5 times in the dropdown. I need the dropdown to list the "Value1, Value2,...Value5" instead.

I have this working where I pull the same information from an Excel spreadsheet (migrating from spreadsheet to SQL) and the array comes in correctly.

---

<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:** [25 November 2024 22:42 UTC](https://discourse.nodered.org/t/sql-array-to-dropdown-just-lists-the-labels/93462/2 "2024-11-25T22:42:05Z")

</div>

In your change node  
set `msg.` `options`  
to `J:` `$$.payload.PumpType;`

This will create an array of values.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [25 November 2024 22:49 UTC](https://discourse.nodered.org/t/sql-array-to-dropdown-just-lists-the-labels/93462/3 "2024-11-25T22:49:47Z")

</div>

You can just alter your SQL to return the correct shape data as per the built in help:

for example `SELECT fieldname as label, otherFieldName as value FROM table` or even just `SELECT PumpType as label, PumpType as value FROM table`

Here is a SQL fiddle for you to check out: [FREE AI-Enhanced Online SQLite Compiler - For learning & practice](https://sqlfiddle.com/sqlite/online-compiler?id=abdf5c06-01de-4193-a6bc-d5094cd07c5a)

---

<div class="post-metadata">

**Author:** ![rbmgf7](https://avatars.discourse-cdn.com/v4/letter/r/c57346/32.png) [@rbmgf7](https://discourse.nodered.org/u/rbmgf7)\
**Post date:** [26 November 2024 15:16 UTC](https://discourse.nodered.org/t/sql-array-to-dropdown-just-lists-the-labels/93462/4 "2024-11-26T15:16:43Z")

</div>

![Screenshot 2024-11-26 091447](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/b/5b1c91414aef14c54d95b57a1710741f5d8b7059.png)

Tried the suggestions but still can't get it to work. Tried some JSONata which this syntax works on the their browser exercise but not sure why it doesn't work inside NR?

---

<div class="post-metadata">

**Author:** ![rbmgf7](https://avatars.discourse-cdn.com/v4/letter/r/c57346/32.png) [@rbmgf7](https://discourse.nodered.org/u/rbmgf7)\
**Post date:** [26 November 2024 16:23 UTC](https://discourse.nodered.org/t/sql-array-to-dropdown-just-lists-the-labels/93462/5 "2024-11-26T16:23:18Z")

</div>

I found a solution by passing it through a function node

```auto
// Assuming msg.payload is your array of objects
// And you want to extract values from a property named 'exampleProperty'

let valuesArray = msg.payload.map(function (obj) {
    return obj.PumpType;
});

// Set the extracted values array to the payload or to a new property to pass it to the next node
msg.payload = valuesArray;

// Return the message object to continue the flow
return msg;

```

---

<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:** [10 December 2024 16:23 UTC](https://discourse.nodered.org/t/sql-array-to-dropdown-just-lists-the-labels/93462/6 "2024-12-10T16:23:48Z")

</div>

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