Skip to main content

Query JSON with SQL

Learn how to use SQL syntax to query data from non-SQL data sources.

You can query data from non-SQL sources as SQL using the built-in Query JSON with SQL resource. This resource uses AlaSQL, and its syntax differs from SQL. For example, AlaSQL uses square brackets or backquotes to enclose column names containing whitespace, rather than double quotes. See the AlaSQL docs for more details. Retool uses AlaSQL version 1.7.3.

Select Query JSON with SQL in the query editor to begin. The Query JSON with SQL resource accepts an array of JSON objects in its FROM clause.

Example of using Query JSON with SQL resource

Query raw JSON​

You can query raw JSON in your FROM clause:

select *
from {{ [{ id: 1, apples: 3 }, { id: 3, apples: 20 }] }}
where apples > 5
order by apples desc;

Join multiple JSON arrays​

Querying JSON with SQL supports traditional SQL joins.

select users.email, payments.userid, payments.id
from {{ usersApi.data }} as users
join {{ paymentsApi.data }} as payments
    on users.id = payments.userid

Query data in an array​

To access an array using AlaSQL, prefix the array with @.

select *
from  {{ usersApi.data }}
where id IN @({{ activeUsersQuery.data }})