Extract field from a SQL block

Community Help
1

Hi all, I’m running an SQL query (inside a sql block) that returns an id with info that is passed to plot. In addition to plot, I’d like to extract the last record from the table, and show a specific value inside a div.

# a sample SQL query -- anything really would do at this stage
'''sql id=test
  select
    'hello' as key,
    'world' as value
  union all
  select
    'howdy' as key,
    'partner' as value
'''

# do some other plot here.
..

# show me the last row from the list, and pull out a specific value

<div class="grid grid-cols-1">
  <div class="card">${test[-1].value}</div>
</div>

One way is to run another SQL query that retrieves the one value alone. This works, however I’m trying to reduce the amount of SQL that needs to run to ensure the code is easily readable and maintainable.

Any suggestions on how I can get this value?

2

JavaScript doesn’t support negative array indexes like that. You have to do it the long way around

<div class="card">${test[test.length - 1].value}</div>

or if you can use newer JS features, you can use Array.at:

<div class="card">${test.at(-1).value}</div>

You may also run into the fact that SQL cells don’t return arrays of objects, they return Arrow result objects. In this case, the code above works fine, but if you need something more familiar you can run

```js
const converted = test.toArray();
```

(by the way if you use three tildes (~) instead of three backticks you can create code blocks that can contain Framework’s triple-back-ticks)

1 Like