SQL Queries

This is where you can build and refine your queries. Queries can then be used on their own or within reports. See Reports for more information.

SQL Query Editor

How does it work?

Jira Cloud itself doesn't offer direct SQL access to its data; it uses Jira Query Language (JQL). This application bridges that gap.

Here's the process:

1. Data Fetching: The app connects to your Jira instance and fetches work item data based on an Work Item Filter you define. This filter can be a JQL query, a saved Jira filter, or (for Jira Software projects) an Agile board or sprint.
2. Local Database Creation: Using the fetched data, the app constructs a relational database directly within your web browser using DuckDB (opens in a new tab). All your Jira data remains local to your browser session; no data is stored on a server.
3. SQL Querying: Once the in-browser database is ready, you can query it using standard SQL. This unlocks powerful and complex analytical capabilities not easily achievable with JQL alone or without exporting data and setting up your own database infrastructure.
4. Query Execution: You can write SQL queries manually in the editor, leverage pre-built templates, or use the AI Prompt feature to help generate queries. Unlike many reporting tools that process data server-side, queries here run directly against the local database.

Performance: Executing SQL queries against the local database is typically very fast. However, the initial step of fetching data from Jira and building the database can take several seconds, depending on the number of work items retrieved by your Work Item Filter.

Parameterization: For dynamic reports, you can parameterize your SQL queries. For example, instead of hardcoding specific status names, you could use a parameter like $status_category which can be set when the report runs, allowing the same query to analyze 'To Do', 'In Progress', or 'Done' work items. See the Parameters section for more details.

SQL Editor

The SQL editor provides a dedicated space for writing and testing your SQL queries. Key features include:

• Syntax Highlighting: Makes your SQL code easier to read and understand by color-coding keywords, functions, and other elements.

SQL Query Editor

• Error Display: Highlights syntax errors and displays execution errors returned by the database after running a query.

SQL error

• Execution Options:
  • Click the Execute button.
  • Enable the Auto Refresh toggle to automatically run the query after any modification.
  • Use the keyboard shortcut: Cmd + Enter (macOS) or Ctrl + Enter (Windows/Linux).

Queries must adhere to the DuckDB SQL dialect. Refer to the DuckDB SQL Introduction (opens in a new tab) for detailed documentation on the syntax and available functions.

The editor also supports query parameterization. See the Parameters section for details on how to use them.

Parameters

You can define parameters within your SQL queries to make them dynamic and reusable, especially when incorporating them into reports. Parameters are denoted using a dollar sign followed by the parameter name, like $parameter_name.

SQL Query Parameters

SQL Query Parameters used in editor

Supported parameter types include:

• string
• number
• boolean
• date
• timestamp

Each of these types can also be used as an array (e.g., an array of strings).

Parameters function similarly to named variables in prepared statement parameters (opens in a new tab) in standard SQL systems. When the query is executed (either directly or within a report), you provide values for these parameters, which are then safely substituted into the query before execution.

Parameterized queries are particularly useful for reports. You can reuse the same query structure multiple times within a single report, simply by providing different parameter values for each instance. For example, you could have a single query designed to calculate the median time work items spend in a particular status category. By using parameters for the status category ($status_category) and work item type ($work_item_type), you could run this query multiple times in one report to show the median time for 'Bugs' in 'In Progress', 'Tasks' in 'In Progress', 'Bugs' in 'Done', etc., without needing to write separate queries for each combination.

Database configuration

The in-browser database is generated based on the data fetched from your Jira work items. For efficiency, the application doesn't fetch every single piece of data for every work item by default. Instead, it retrieves only the fields necessary for your analysis.

For example, if you want to calculate the time an work item spent in various statuses, the application needs to fetch the "Status" field and the work item's historical data (changelog).

You can control which fields are fetched using the Configure modal.

Database configuration modal

In this modal, you can:

• Select the specific Jira fields required for your queries. (Note: While many common fields are supported, some may not be available yet. You can request support for additional fields.)
• Choose whether to include historical data (work item changelog), which is essential for time-based analysis like cycle time or time-in-status calculations.

Once the fields are changed, the application may need to re-fetch the work items and rebuild the database.

Configuring the database correctly ensures that you have the necessary data for your SQL queries while optimizing the initial data fetching time.

Database Schema

The application generates a relational database schema based on the fetched Jira data and the configured fields. Here's an example structure:

CREATE TABLE fields(
    id VARCHAR UNIQUE,
    "key" VARCHAR,
    "name" VARCHAR,
    custom BOOLEAN,
    "schema" JSON
);
 
CREATE TABLE histories(
    work_item_id VARCHAR,
    date TIMESTAMP,
    field_id VARCHAR,
    "value" VARCHAR,
    seconds_in_state INTEGER,
    UNIQUE(work_item_id, date, field_id)
);
 
CREATE VIEW work_items AS
    SELECT id, "key", status, assignee, issuetype
    FROM __internal_work_items_all WHERE ((__internal_work_item_sources & 2) > 0);
 
--
-- Tables based on Jira fields select in database configuration
--
CREATE TABLE issuetypes(id VARCHAR UNIQUE, "data" JSON);
 
CREATE TABLE statuses(
    id VARCHAR UNIQUE,
    "name" VARCHAR,
    description VARCHAR,
    iconUrl VARCHAR,
    "data" JSON
);
 
CREATE TABLE users(id VARCHAR UNIQUE, "data" JSON);
 
--
-- View showing human readable values (e.g. names instead of IDs)
--
CREATE VIEW work_items_display AS
    SELECT __internal_work_items_all.id AS id
        __internal_work_items_all."key" AS "key",
        json_extract_string(statuses."data", 'name') AS status,
        json_extract_string(users_assignee."data", 'displayName') AS assignee,
        json_extract_string(issuetypes."data", 'name') AS issuetype
    FROM __internal_work_items_all
    LEFT JOIN statuses
        ON ((statuses.id = __internal_work_items_all.status))
    LEFT JOIN users AS users_assignee
        ON ((users_assignee.id = __internal_work_items_all.assignee))
    LEFT JOIN issuetypes
        ON ((issuetypes.id = __internal_work_items_all.issuetype))
    WHERE ((__internal_work_items_all.__internal_work_item_sources & 2) > 0);

The database schema is displayed next to the SQL Editor:

Database Schema

Key Tables and Views:

• work_items (View): This view represents your Jira work items. Each column corresponds to a Jira field you selected in the configuration. Column names are the Jira field IDs (e.g., status, assignee, issuetype, customfield_10010). These columns might contain simple values or foreign keys referencing other tables.

• work_items_display (View): This is a convenience view built on top of the underlying work items table (__internal_work_items_all). It automatically joins common fields like status, assignee, and issuetype with their respective lookup tables (statuses, users, issuetypes) to display human-readable names instead of raw IDs.

• fields (Table): Contains metadata about all available Jira fields, including their ID (id), key (key), name (name), whether they are custom (custom), and their schema definition (schema). The id from this table corresponds to the column names in the work items view.

• histories (Table): Stores the historical changes for work item fields (if historical data is configured). Each row represents a specific value a field held for an work item over a period.

  • work_item_id: The ID of the work item.
  • date: The timestamp when the field changed to this value.
  • field_id: The ID of the field that changed.
  • value: The ID or value the field changed to.
  • seconds_in_state: The duration (in seconds) that the work item spent with the field value specified in the same row. This is crucial for calculating time-in-status or cycle time metrics.

• Lookup Tables (issuetypes, statuses, users, etc.): These tables store details about specific Jira entities referenced by ID in the work items or histories tables. They typically contain an id column and a data column (often JSON) holding detailed information about the entity (e.g., name, description).

Understanding this schema is key to writing effective SQL queries against your Jira data.

Custom tables

Sometimes, you might need data that isn't available directly within Jira for your analysis. This could include information like national holidays, specific team allocations, or other project-specific metadata. Custom tables allow you to incorporate this external data into your in-browser database.

You can add custom tables via the Configure modal, under the Custom Tables tab.

Custom tables in Configure database modal

Add custom table

The process involves:

1. Uploading a CSV file: Provide your custom data in a standard Comma Separated Values (CSV) format.
2. Defining Column Types: Specify the data type (e.g., string, number, date) for each column in your uploaded CSV to ensure it's correctly interpreted by the database.

Once defined, the data from your custom table is saved alongside your query configuration. When the database is built, this custom data is loaded into its own table, allowing you to join it with your Jira data in SQL queries.

Writing queries

While the application provides a fully functional DuckDB instance running directly in your browser, allowing for complex data manipulation, it's generally recommended to primarily use SELECT statements for querying your Jira data.

Modifying the database schema (e.g., using CREATE TABLE, ALTER TABLE, or DROP TABLE) within your queries can lead to unpredictable results, especially when the query is executed as part of a report alongside other queries. Such modifications might interfere with the expected database state for subsequent queries or report components.

Additionally, you can leverage the AI Prompt feature to help generate, improve, or fix your SQL queries. See the SQL Query Examples page for practical examples.

For detailed information on the SQL syntax and functions supported by DuckDB, refer to the official DuckDB SQL Introduction (opens in a new tab).

Exporting data

After running your SQL query, you can export the results for further analysis or use in other tools. The results can be downloaded in two common formats:

• CSV (Comma Separated Values): A simple text-based format where data is organized in rows, with values separated by commas. CSV files are highly compatible and can be easily opened and manipulated in spreadsheet software like Microsoft Excel, Google Sheets, or Apple Numbers. They are ideal for sharing smaller datasets or for quick viewing and basic analysis.

• Parquet: A highly efficient, columnar storage format optimized for big data processing frameworks. Parquet files store data column by column, which leads to better compression and faster query performance, especially when dealing with large datasets and selecting specific columns. This format is well-suited for data warehousing, data lakes, and analysis using tools like Apache Spark, Pandas (in Python), or other data science platforms.

Choose the format that best suits your needs based on the size of your data and how you intend to use it downstream.

Using AI to generate SQL queries

The AI Prompt feature helps you craft effective prompts for Large Language Models (LLMs) to generate or modify SQL queries.

Generate AI Prompt modal

It works by combining several pieces of information:

1. Database Schema: The structure of your database tables and columns.
2. Current SQL Query (Optional): Existing SQL query in the editor you want to refine or use as a starting point.
3. Your Request: A natural language description of what you want the SQL query to achieve (e.g., "Show me all users created in the last month").

The feature then synthesizes these inputs into a detailed prompt. This prompt provides the LLM with the necessary context (schema, existing code) and your specific goal.

You can then copy this generated prompt and paste it into your preferred LLM, such as:

• ChatGPT
• Claude
• GitHub Copilot Chat
• Google Gemini

This process helps the LLM understand your database structure and requirements accurately, leading to better SQL query generation.