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(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:

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

You can extract the load detail with the following query:

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:

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.

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

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.

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.

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.

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.

Need help?

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