How can I use SQL window functions in Query JSON?

Queries and Resources
,
1

There are Query JSON with SQL resources in Retool. I want to calc cumulative sum based on field I received from API request. It calculates wrong in Query JSON with SQL. Is there any way to do it correct?

Example (works right when data from Postgres):
select
date,
sum(metric) over (order by date) as cum_metric
from my_table

Result
date, cum_metric
2021-01-01, 10
2021-01-02, 20
2021-01-03, 30

Example (works wrong when data from API):
select
date,
sum(metric) over (order by date) as cum_metric
from my_table

Result
date, cum_metric
, 60

2

Hi @yvkhro!

In the left panel, what is the data structure returned from the API? You may need to transform it to be similar to the data structure returned from Postgres. Generally, APIs return data as an array of objects and SQL dbs (like Postgres) returns data as an object of arrays. You can use something like the formatDataAsObject(apiQuery.data) method. Let me know how this works for you!
select
date,
sum(metric) over (order by date) as cum_metric
from formatDataAsObject(apiQuery.data)

1954×956 88.5 KB