Query anything with SQL
You can query data from non-SQL sources via SQL. If your data isn't from a SQL database, it's probably an array of objects. Retool lets you use SQL to query your data as if they were tables in a database. That means you can query the results of a REST API endpoint, or Google Sheets data, all via SQL.
Writing a query to transform JSON objects
If you have your array of JSON objects handy (eg. in apiData.data
), you can query it via a Query JSON with SQL
resource:
Differences from standard SQL
Data isn't actually stored in a SQL database like Postgres, so some SQL functionality might not work. For example, AlaSQL uses square brackets
[]
or backquotes to enclose column names containing whitespace instead of double quotes""
. If something isn't working, see the AlaSQL docs for more information:
Querying raw JSON
You can query raw JSON in your FROM
clause:
select
*
from
{{ [{ id: 1, apples: 3 }, { id: 3, apples: 20 }] }}
where
apples > 5
Joining two JSON arrays
Querying JSON with SQL supports traditional SQL joins.
select *
from {{ usersApi.data }} as users
join {{ paymentsApi.data }} as payments
on users.id = payments.userid
Data from SQL queries are NOT returned as an array of objects!
If you want to use the data as an array of objects, you can use the helper function
formatDataAsArray
to do so like below.{{ formatDataAsArray(sqlQuery.data) }}
Use AlaSQL to add aliases to column names
Use the following syntax in the select
portion of your query to add an alias to a column.
SELECT ColumnNameFromAnotherQuery as `Alias Name` from {{your_query.data}}
...
Updated 11 months ago