SQL restructuring and confusion about what the sql block returns

New To Observable
1

I’m new to Observable Framework and I’m not a javascript programmer. I’m trying to port a dashboard I created successfully with Quarto + Observable + DuckDB to Observable Framework and I’m running into some problems with simple things.

Specifically, I want to use the results of a sql query to populate a select input. I have this block, but the resulting target_list is a key-value array. I don’t seem to be able to use things like map() on this object. Arrow Table functions also don’t seem to be available. I though the sql block returned an Arrow table, so I’m confused about what I’m dealing with.

```sql id=target_list
SELECT DISTINCT Target FROM targets
```

I tried destructuring but that only ever seems to return the first row, or in the latter case, the first result broken up by letter. I really don’t understand what’s going on here. What I’m trying to accomplish here seems like it would be a common pattern (though I can’t find an example in which an Input is populated by a sql query result) – there’s an easy way to accomplish this, right?

```sql id="[{'Target': target_list2}]"
SELECT DISTINCT Target FROM targets
```

```sql id=[target_list3]
SELECT DISTINCT Target FROM targets
```
2

The result is an Arrow table, you can transform it with, e.g.:

Array.from(target_list, (d) => ({…d})

or table.toArray

target_list.toArray()

In the future please open a GitHub Discussion instead. We’re using GitHub now for support around our open-source projects. Thank you!

3

Yes, please use a GitHub Discussion for open-source related topics — we’d really like to consolidate discussions there so people don’t have to search in multiple places to find help.

The ARRAY SQL function might be helpful here. This lets you return a single row with multiple values, rather than multiple rows. Then you can destructure the array-valued column into a variable:

```sql id=[{targets}]
SELECT ARRAY(SELECT DISTINCT Target FROM targets) AS targets;
```

Technically this will give you a Utf8Vector rather than a string[], but since Arrow vectors are iterable, you can use it directly to create an input. For example, here’s a radio input:

```js
const target = view(Inputs.radio(targets), {label: "Target"});
```

The value of target is now a string (or null if the user hasn’t chosen a target yet, but you can also preset the initial value of the radio if you like).

1 Like