BSQL Reference

When you use Data Blaze tables in your Text Blaze snippets, Text Blaze queries data using the Blaze Structured Query Language (BSQL). BSQL is based on the industry standard language for querying databases: SQL.

When you edit commands to load or save data to Data Blaze, you may be using the graphical editor to select what data to load and save. Behind the scenes these selections are converted to BSQL. In addition to using the graphical editor, you can also view and write BSQL directly. Doing so gives you access to some powerful features that are not available in the graphical editor.

SELECT Queries - Querying data from Data Blaze

Basic SELECT query

A basic BSQL command looks something like:

SELECT
  Name,
  Age
FROM
  People

This loads all the rows from the People table. It only loads the Name and Age fields from each row though. If you have other fields in your space, they will be omitted.

The following example uses this query to pull data from a space:

You can use the Blaze formula language to modify the results prior to returning them. For example:

SELECT
  Name,
  Age/2 AS half_age
FROM
  People

This will load the Name as before. Instead of loading the Age directly though, it will divide the Age by 2 and return it in a new value called "half_age". "half_age" is an alias and you can use aliases to rename any field in the BSQL results. When modifying results values like we do with Age here, an alias is always required.

BSQL supports most of the Blaze formula language, so you can do more complex logic. For example, if we had another field called "Gender", we could use something like the following to return a nicely formatted name:

SELECT
 Name,
 Gender,
 Age,
 "Mr " & Name if Gender = "Male"
   else "Ms " & Name AS `formatted name`
FROM
 People

WHERE clauses

You can also include conditions in your BSQL by using a WHERE clause after the FROM clause. For example, if we only wanted to get people over 65, we could use:

SELECT
  Name,
  Age
FROM
  People
WHERE
   Age > 65

The WHERE clause will cause Data Blaze to only return matching results. As with the fields you return, you can use most of the Blaze formula language in the WHERE clause so something like this is also possible:

SELECT
  Name,
  Age
FROM
  People
WHERE
  Age > 65 OR Age < 20

GROUP BY clauses and aggregations

Imagine you wanted to know the number of people you had of each age. You could do this by loading all the data into Text Blaze and tallying it up. However, if you had a lot of data, that could be slow.

BSQL supports aggregating data using the GROUP BY clause and lets you do aggregations like this directly within BSQL. For example, the following will group the data by each age and the countagg() function will count up the number of people in each group.

SELECT
  age,
  countagg() AS count
FROM
  People
GROUP BY age

The results of this might look something like:

In addition to countagg() there is also maxagg() (maximum value in a group), minagg() (minimum value in a group), avgagg() (average value in a group), sumagg() (sum of values in a group). For example, if we wanted to get a breakdown of average age by gender, we could use:

SELECT
  gender,
  avgagg(age) AS average_age
FROM
  People
GROUP BY gender

You can group by multiple fields, if you wanted to get a breakdown by both gender and age, you could use something like:

SELECT
  age,
  gender,
  countagg() AS count
FROM
  People
GROUP BY age, gender

HAVING to apply a condition after grouping

WHERE clauses filter results prior to any grouping implemented by a GROUP BY. If you want to filter results after grouping, you can use the HAVING clause.

For example if we wanted to only get categories where the average age was above 40, we could use:

SELECT
  gender,
  avgagg(age) AS average_age
FROM
  People
GROUP BY gender
HAVING avgagg(age) > 40

ORDER BY to order results

By default, when there are multiple results, they are returned in a random order. If you want multiple results to be returned in a specific order, you can use the ORDER BY clause.

This will return results with the oldest people first (order by age descending):

SELECT
  name,
  age
FROM
  People
ORDER BY age DESC

This will return results with the youngest people first (order by age ascending):

SELECT
  name,
  age
FROM
  People
ORDER BY age ASC

You can also order by multiple fields. This will order by age primarily and name secondarily:

SELECT
  name,
  age
FROM
  People
ORDER BY age ASC, name ASC

LIMIT to limit the number of results returned

The LIMIT clause will limit how many results Data Blaze returns. For example, if you wanted the name of the youngest person in the table, you could use:

SELECT
  name
FROM
  People
ORDER BY age ASC
LIMIT 1

The LIMIT 1 tells Data Blaze to return at most one row.

UPDATE queries to update data

An UPDATE query changes data in an existing row in a table.

This example sets the age of "John" in the table to 100. For the BSQL to run successfully there must be a row in the table with the name "John". Also there can only be one such row. If multiple rows exist with the name "John" this query will fail.

UPDATE People
SET age=100
WHERE name = "John"

Update statements may also set values based on existing values. Suppose it is John's birthday and we want to increase his age by one, we could use:

UPDATE People
SET age=age + 1
WHERE name = "John"

INSERT queries to add rows

An INSERT query adds a row to a table.

This example shows how you could add a new row to the table.

INSERT INTO People
SET age=100, name="John", gender="Male"

You can also insert multiple rows at a time in one of two ways.

First, you can use a list of positional lists:

INSERT INTO People (age, name, gender)
VALUES [
  [100, "John", "Male"],
  [40, "Jane", "Female"]
]

Second, you can use a list of keyed lists:

INSERT INTO People
VALUES [
  ["age": 100, "name": "John", "gender": "Male"],
  ["age": 40, "name": "Jane", "gender": "Female"]
]

DELETE queries to remove rows

A DELETE query removes a row from the table.

This removes "John"'s row from the table. There must be one row and only one row that matches the WHERE clause or the query will fail to execute.

DELETE FROM People
WHERE name = "John"

Additional tips

Including commands in BSQL

You may include Text Blaze commands in your BSQL. For example imagine you had a formtext containing an age and you wanted to use it to filter the results. If the formtext had the name "min_age" you could use this BSQL:

SELECT
  Name,
  Age
FROM
  People
WHERE
  Age > {=min_age}

For form variables, BSQL supports a shorthand syntax for using their values in BSQL. Just add an @ to the form variable name in the BSQL:

SELECT
  Name,
  Age
FROM
  People
WHERE
  Age > @min_age

Referencing the current user

If you have a snippet being used by multiple people in the team, you may want to customize results by the user doing the query. You can do that by using the currentuser() BSQL function which returns the email address of the user running the query.

Working with Linkrow fields

Linkrow fields have special handling in Data Blaze. To see how they work, let's imagine we have a Linkrow field called "Contacts" that links to a table where the primary field is the contact's name.

If we do the following:

SELECT
  Contacts
FROM
  People

This will return a list of the primary field of the contacts (their names in our case).

This Contacts field is read only in BSQL so you can use it in SELECT, but you can't update or insert anything into it as part of a BSQL query.

To support modifying Linkrow fields, BSQL has a special syntax. You can add "->id" to the end of the Linkrow name in BSQL and Data Blaze will use the id's of the linked row instead of the primary field value.

We can update our query above to use this syntax:

SELECT
  `contacts->id`
FROM
  People

Now this will return a list of the contact id's instead of their primary field values.

When working with Linkrow id's you can also update or insert values. For instance, imagine we had a row in the Contacts table with the id 59LlbdZnsFOz22GOuSKjyM. We could set John to have that contact with:

UPDATE People SET
  `contacts->id`=["59LlbdZnsFOz22GOuSKjyM"]
WHERE
  name = "John"

Note that when we want to set a Linkrow field to a single item, the brackets are optional, so the following is also valid:

UPDATE People SET
  `contacts->id`="59LlbdZnsFOz22GOuSKjyM"
WHERE
  name = "John"

Lookups

Data Blaze has a Lookup field type that allows you to pull a specific field of data from another table. You use Lookup fields in BSQL.

Additionally, BSQL has a lookup() function that can be used to lookup data without needing to create a separate field.

lookup() takes two parameters: the first is the name of the Linkrow field in the primary table, and the second is the name of the column you want to look up in the linked table.

For example if we wanted to get all the numbers from the Phone Number field of the Contacts table for each person we could use:

SELECT
  name,
  lookup(Contacts, `Phone Number`) as numbers
FROM
  People

BSQL Compatibility with Blaze Formulas

The following functions are supported in BSQL but not in Blaze Formulas:

• countagg()
• sumagg()
• avgagg()
• maxagg()
• minagg()
• rowid()
• currentuser()
• link()
• button()

The following Blaze Formula functions are not currently supported in BSQL:

• base()
• filter()
• map()
• reduce()
• keys()
• fromjson()

The following Blaze Formula features are not currently supported in BSQL:

• Named lists
• Nested positional lists
• Lambda functions
• datetime() functions do not support custom locales. The following formatting tokens are unsupported: x, X. The following tokens may have slightly different behavior than in Text Blaze: dd, e, d, do, k, kk.
• Code blocks