SQL Queries

SQL is the key to unlocking your database's potential. In our Dashboards, SQL lets you fetch and display data precisely how you want.

In querying databases, you can utilize statements including GROUP BY, ORDER BY, HAVING, JOIN, LIMIT, UNION, among others, to refine, structure, and organize your query results as per the requirements.

Extracting JSON data

The JSONExtractString function is essential when working with databases that store data in JSON format, as it allows for the extraction of specific string values from a JSON field. This function parses the JSON data, enabling precise retrieval of the data you need from the JSON structure.

Here's a basic syntax of the function

JSONExtractString function
JSONExtractString(json, 'field')

The function accepts two arguments:

  • json represents the JSON data column.
  • 'field' is a path to the specific data you want to extract.

For example:

JSON data
{
    "heroku": {
        "status": "active",
        "details": {
            "load": "medium",
            "uptime": "99.9%"
        }
    }
}

You can extract the load detail with the following query:

Extract JSON data
SELECT JSONExtractString(json, 'heroku', 'details', 'load') as LoadDetail
FROM {{source}};

This would return medium as the value for LoadDetail.

Exploring Basic JSON Structure

If you would like to understand the basic structure of your JSON data before diving into more complex queries with JSONExtractString, you can use the Table chart and the following query:

Table view - JSON Structure
SELECT 
    {{time}} AS time, 
    json
FROM 
    {{source}}
WHERE 
    time BETWEEN {{start_time}} AND {{end_time}}
LIMIT 10

Querying with Examples

Let's dive into some common queries and scenarios.

1. Fetching Log Levels

To fetch log levels, we will use the JSONExtractString function to extract the 'level' from the JSON data. We then group the results based on the time interval and the log level.

Fetch Log Level
SELECT {{time}} AS time, 
       count(*) as value, 
       JSONExtractString(json, 'level') as level
FROM {{source}}
WHERE time BETWEEN {{start_time}} AND {{end_time}}
AND level != ''
GROUP BY time, level

Keep an eye on the Axes

By default, there needs to be at least a time as X-axis column and value for an Y-axis column. By renaming the value in the SELECT statement, you'll also need to replace the expected Y-axis column. The same goes in case you'd use the series Axis.

2. Fetching Part of a Message

To search for a specific part of a message, we can use the ILIKE operator, which performs a case-insensitive search.

Fetching Part of a Message
SELECT {{time}} AS time, 
       count(*) as value
FROM {{source}}
WHERE message ILIKE '%connection lost%' 
AND time BETWEEN {{start_time}} AND {{end_time}}
GROUP BY time
  • ILIKE: Case-insensitive search for a specified pattern.

3. Monitoring Response Times

In another example, to track average response times, we use the JSONExtract function to retrieve 'executionTime' values from the nested JSON structure. By grouping the results by time intervals, we get a time-series visualization of how our system's response time varies.

Average Response Time
SELECT 
    {{time}} AS time, 
    AVG(JSONExtract(json, 'context', 'properties', 'executionTime', 'Nullable(Float)')) AS value
FROM 
    {{source}}
WHERE 
    time BETWEEN {{start_time}} AND {{end_time}} 
    AND JSONExtract(json, 'context', 'properties', 'executionTime', 'Nullable(Float)') IS NOT NULL
GROUP BY time

Other Basic Operators

1. Using the LIKE Operator

While ILIKE provides a case-insensitive match, LIKE is case-sensitive.

 
WHERE level LIKE 'Error%';

This will find all messages that start with the word "Error".

2. Using Equal (=)

To fetch records with a specific value:

 
WHERE JSONExtractString(json, 'status') = 'active';

This will extract records where the "status" in the JSON is "active".

3. Using != for Not Equal

If you want records that don't match a particular value:

 
WHERE JSONExtractString(json, 'status') != 'inactive';

Variables in SQL Queries

Variables can be dynamically inserted into SQL queries to customize the data fetched. These variables can be defined either as required or optional.

  • Required Variables: These are necessary for the SQL query to run. If not provided, an error will be raised. They are denoted using the double curly braces, e.g., {{variable_name}}.

  • Optional Variables: They are denoted by double square brackets enclosing a block that contains a variable, e.g., [[...{{variable_name}}...]]. If the variable inside the optional block is not provided, the whole block is removed from the query.

Example of using both Required & Optional variables
SELECT 
    {{time}} AS time, 
    count(*) as count
FROM 
    {{source}}
WHERE 
    JSONExtractString(json, 'message', 'additional_info', 'headers', 'User-Agent') ILIKE '%' || {{user_agent}} || '%' 
AND 
    time BETWEEN {{start_time}} AND {{end_time}}
[[AND 
    JSONExtractString(json, 'level') = {{log_level}}]]
GROUP BY 
    time

In this code block, our custom {{user_agent}} is required variable, while {{log_level}} is an optional variable. The SQL query will still run if {{log_level}} is not provided, but it will further filter the results if it is provided.

Types of Variables

  • String: Replaced with a string value. Denoted by single quotes, e.g., 'value'.
  • Number: Directly replaced with a numeric value.
  • Date: Converted to a date format.
  • DateTime: Converted to a DateTime format with microseconds precision.
  • Source: This special type represents the data source.

To utilize these variables in the SQL query:

  1. Define the Variable: Ensure that the variable is defined in your context.

  2. Insert the Variable in the SQL Query: Use the appropriate format, such as {{variable_name}} for required variables.

Common Obstacles

  1. MissingVariableError: This error will be raised if a required variable is missing its value. Make sure to fill in your variable or remove it.
  2. Unknown Variable Type Error: This is triggered when a variable type is not recognized. Please refer to the Types of Variables.

Using variables in SQL queries offers a dynamic way to fetch and visualize data based on changing criteria or user inputs. Ensure that the variables are correctly defined and interpolated before running the SQL query to prevent errors.

You can also refer to the ClickHouse SQL , which is used to build the queries.

Need help?

Please let us know at hello@betterstack.com.
We're happy to help! 🙏