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:

It's not *exactly* SQL

Since the data isn't actually stored in a SQL database like Postgres, some SQL might not work. If something isn't working exactly as expected, you can reference 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) }}

Updated 24 days ago


Query anything with SQL


Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.