SQL Fields
SQL fields are added to a data set, which is defined using the native SQL dialect of your database.
This allows you to perform simple tasks such as concatenating two fields together, performing math calculations between fields, or extracting a value from a nested JSON blob.
Adding a SQL field
- In the UI of the Query Engine, select the data set you wish to add the field to.
- Scroll to the bottom of the list of existing fields in the data set, and click "Add property"
- Add a new "SQL Field"
- Select the output data type of the SQL snippet you write.
- Enter in the SQL snippet for the field.
- Click "save"
SQL Snippet Examples
Concatenating strings
A basic usage is joining strings together such as first_name and last_name.
CONCAT(first_name, ' ', last_name) as "full_name"Performing maths
You might have two fields that you need to perform a mathematic equation between, such as calculating total price of an order.
(unit_price * unit_order_quantity) as "total_order_price"Note, we are not formatting the price to a string here, the result is still a number. This allows future aggregations to be ran over the total order price, and formatting can be applied on the dashboard itself later on.
Picking values from JSONb fields
Many applications use JSONb fields to dump unstructured data. However often there are fields we know to exist in that JSON structure that we want to use on our dashboard, here is how we can make them available!
my_nested_json_field->'some_key_in_the_json_object' as "a_new_name_for_the_extracted_field"Note, using JSONb fields extensively can impact performance if your database has not been sufficiently indexed!