Querying JSON using SQL

We can filter JSON values by implementing SQL queries on it. This is helpful to query JSON values within the controls. The idea is to work on NoSQL data sources using SQL queries.

If your information isn’t from a SQL data set, it’s most likely an array of objects. Studio allows you to utilize SQL to query your data as though they were tables in a data set. That implies you can query from various data responses and results, such as a REST API endpoint, Google Sheets connectors, and more which gives JSON data, all through SQL -like queries.

In the bind data section under Data, you can see the JS/SQL to bind JSON data using SQL -like query. This is actually an editor for AlaSQL where we can perform all the SQL-like queries such as selecting data, changing names, joining two data sources, and more.

We have AlaSQL in the backend which enables all these queries to run. Users can pass references of controls and variables using keywords in “ {{ }} “.

NOTE: The data is not actually stored in the SQL database, so all SQL formats might not work in the editor. This actually is AlaSQL editor which uses AalSQL format for writing queries. To understand more about AlaSQL and its format, do refer to its documentation.

AlaSQL
readme · AlaSQL/alasql Wiki (github.com)
AlaSQL Keywords · AlaSQL/alasql Wiki (github.com)

Using AlaSQL in data builder

We have the AlaSQL editor in the data bind section, as the data builder. This is used to write SQL –like queries with AlaSQL syntax to perform queries on raw JSON data

select department, sum(CAST([sales] as INT)) from {{datastore}} group by department

In the above query we have written a SQL-like syntax where we have selected columns/attributes and grouped it by a different attribute. This is supported by AlaSQL so users can refer to the documentation of AlaSQL to know more about its syntax

We also have JS editor with support of AlaSQL. It requires different syntax to declare variables.

{{alasql("select department, sum(CAST([sales] as INT)) from ? group by department", Array(datastore))}}

In the above query, we have written a syntax for JS builder which is processing a query with the support of AlaSQL library.

Performing queries on raw JSON

Let us now see how to perform different types of SQL-like queries on raw JSON data. Studio supports every syntax with clauses of AlaSQL.

Select and From- query on raw JSON

You can query the JSON data using SELECT and FROM clauses.

select * from {{datastore}} where department = {{textinput}}

In the above query, we are using the SELECT clause to choose the columns or attributes of the JSON data and the FORM clause is used to specify where to fetch the data from. We have raw JSON stored in data store control which we are passing in the query as the reference keyword.

Sort, Group, and Aggregate – query on raw JSON

There are various ways you can manipulate the data of raw JSON using queries in AlaSQL editor.
Let’s take an example where we want to sort the data in descending order with respect to a column or attribute of JSON data.

select * from {{datastore}} order by sales desc

In the above query, we are sorting the data by using ORDER BY clause and specifying the order of sort too. With this query, our data will get sorted in descending order with respect to values in sales attributes.

Let’s take an example where we want to group the data as well as generate aggregate values with respect to distinctive columns or attributes of JSON data.

select department, sum(CAST([sales] as INT)) from {{datastore}} group by department

In the above query, we are grouping the JSON data with respect to department attribute using the GROUP BY clause and also performing the SUM clause to aggregate sum values of the sales attribute thereafter displaying it with respect to grouped data.

Joining two JSON datastore arrays

We can use query with the JOIN clause to join data from two datastores too.

select * from {{datastore1}} as facts join {{datastore2}} as cities on cities.id = facts.id

In the above query, we are joining two datastore values using the JOIN clause and providing them with alias names too which are combined on similar id values using the ON clause. The aliases are useful to further use in the query syntax as it is user friendly and easy to implement.

You can use all other types of joins too such as inner join, outer join, and more.

Adding aliases on column names

You can add an alias to a column while querying raw JSON using SQL.

select product as 'Product Name' from {{datastore}}

In the above query, we are putting up an alias of Product Name to the product attribute after the SELECT clause.

NOTE: To access columns with names having ' . ' and other such characters then the query to access that column should have a column name between the" ` "(backquote) character.
Example:

Using AlaSQL in Custom JavaScript

The implementation of querying JSON data using SQL can also be performed in Custom JS under the data bind section too.

The studio JS editor has the support of the AlaSQL library; therefore, we can just use simply it in the JS editor using alasql(), and we can perform SQL queries on the raw JSON data.

output = alasql("select * from ? where department = ?", [data, input])

In writing the syntax we have to add the variables by using the ? sign and declare it at the end. These variables constitute the data of keywords for controls.

NOTE: You can take advantage of AlaSQL to run queries on raw JSON data in any JS editor in the studio.