Schema for Jira family of products
The Jira family of products share many of the same tables. In other words, data for the available Jira products in the Atlassian Data Lake are aggregated into the same set of tables.
If you need to filter for specific Jira product data, use the Project type column from the Jira project table in the “Filters” section of your visual mode queries or the WHERE clause of your SQL mode queries.
Columns with an asterisk (*) are only available when you include all data for your products in a Data Lake connection. Learn more about how to set the scope of data for Atlassian Data Lake connections.
The diagram above shows the relationships between the schema for the Jira family of products and the schema for Jira Service Management. Columns in bold are the primary keys of the table that they’re in.
Tables for project data
There are several tables for Jira project data:
Project
Project component
Project fix version
Project
The jira_project table lists your Jira projects.
The table uses the following columns:
Assignee type*
The default issue assignee setting for the project.
SQL name | assignee_type |
|---|---|
Data type | String |
Example values | PROJECT_LEAD, UNASSIGNED |
Description*
The description of the project.
SQL name | description |
|---|---|
Data type | String |
Lead account ID
The unique identifier of the person defined as the project lead.
SQL name | lead_account_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the account table in the schema for organization data. |
Name*
The name of the project.
SQL name | name |
|---|---|
Data type | String |
Project category*
The project category that this project is assigned to.
SQL name | project_category |
|---|---|
Data type | String |
Project ID
The unique identifier of the project. Use this as the primary key for project data.
SQL name | project_id |
|---|---|
Data type | String |
Project key*
The unique key for the project.
SQL name | project_key |
|---|---|
Data type | String |
Project reference*
The identifier of the project within a Jira site.
It can be used to associate this record with the project identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.
SQL name | project_ref |
|---|---|
Data type | Number |
Project type*
The type of the project. Learn more about project types.
SQL name | project_type |
|---|---|
Data type | String |
Example values |
|
Row refreshed at
The last refresh time (UTC time zone) for this record.
SQL name | max_row_refreshed_at |
|---|---|
Data type | Datetime |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Project component
The jira_project_component table lists the components defined for each project.
The table uses the following columns:
Assignee type*
The default issue assignee setting for the project.
SQL name | assignee_type |
|---|---|
Data type | String |
Example values | PROJECT_DEFAULT, UNASSIGNED |
Component ID
The unique identifier of the project component. Use this as the primary key for component data.
SQL name | component_id |
|---|---|
Data type | String |
Component reference*
The identifier of the component within a Jira site.
It can be used to associate this record with the component identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.
SQL name | component_ref |
|---|---|
Data type | String |
Description*
The description of the project component.
SQL name | description |
|---|---|
Data type | String |
Lead account ID
The unique identifier of the person defined as the component lead.
SQL name | lead_account_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the account table in the schema for organization data. |
Name*
The name of the project component.
SQL name | name |
|---|---|
Data type | String |
Project ID
The unique identifier of the project.
SQL name | project_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_project table. |
Row refreshed at
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
|---|---|
Data type | Datetime |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Project fix version
The jira_project_fix_version table lists the versions defined for each project.
The table uses the following columns:
Description*
The description of the project version.
SQL name | description |
|---|---|
Data type | String |
Name*
The name of the project version.
SQL name | name |
|---|---|
Data type | String |
Project ID
The unique identifier of the project.
SQL name | project_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_project table. |
Release date
The release date of the project version.
SQL name | release_date |
|---|---|
Data type | Date |
Row refreshed at
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
|---|---|
Data type | Datetime |
Sort order*
The sequence order of versions, matching their order of appearance in the Jira product.
SQL name | sort_order |
|---|---|
Data type | Number |
Start date
The start date of the project version.
SQL name | start_date |
|---|---|
Data type | Date |
Status*
The current status of the project version.
SQL name | status |
|---|---|
Data type | String |
Version ID
The unique identifier of the project version. Use this as the primary key for version data.
SQL name | version_id |
|---|---|
Data type | String |
Version reference*
The identifier of the project version with a Jira site.
It can be used to associate this record with the version identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.
SQL name | version_ref |
|---|---|
Data type | String |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Tables for issue data
There are several tables for Jira issue data:
Issue
Issue affected version mapping
Issue cycle time
Issue custom fields
Issue group mapping
Issue field
Issue fix version mapping
Issue history
Issue status history
Issue
The jira_issue lists your Jira issues.
The table uses the following columns:
Assignee account ID*
The unique identifier of the person assigned to the issue.
SQL name | assignee_account_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the account table in the schema for organization data. |
Created at
The date and time (UTC time zone) when the issue was created.
SQL name | created_at |
|---|---|
Data type | Datetime |
Creator account ID*
The unique identifier of the person who created the issue.
SQL name | creator_account_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the account table in the schema for organization data. |
Description*
The description of the issue.
SQL name | description |
|---|---|
Data type | String |
Due date
The due date (UTC time zone) of the issue.
SQL name | due_date |
|---|---|
Data type | Date |
Environment*
A short description of the environment in which the issue occurred.
SQL name | environment |
|---|---|
Data type | String |
Example value | linux |
Is archived
Indicates whether or not the issue is currently archived.
SQL name | is_archived |
|---|---|
Data type | Boolean |
Issue ID
The unique identifier of the issue. Use this as the primary key for issue data.
SQL name | issue_id |
|---|---|
Data type | String |
Issue key*
The number of the issue within the project.
SQL name | issue_key |
|---|---|
Data type | String |
Example value | TEST-12 |
Issue reference*
The identifier of the issue within a Jira site.
It can be used to associate this record with the issue identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.
SQL name | issue_ref |
|---|---|
Data type | String |
Issue type*
The type of issue.
SQL name | issue_type |
|---|---|
Data type | String |
Example values | Task, Story |
Labels*
An array of label names added to the issue.
SQL name | labels |
|---|---|
Data type | Array of strings |
Original time estimate (seconds)*
This column requires time-tracking enabled.
The original time estimate (in seconds) that was set.
SQL name | original_time_estimate_secs |
|---|---|
Data type | Number |
Parent issue ID
The unique identifier of the immediate parent of the issue, if the issue uses the “Parent” field.
SQL name | parent_issue_id |
|---|---|
Data type | String |
Priority*
The current issue priority.
SQL name | priority |
|---|---|
Data type | String |
Priority sort order
The sequence order of the priority, matching its order of appearance in the Jira product.
SQL name | priority_sort_order |
|---|---|
Data type | Number |
Project ID
The unique identifier of the project.
SQL name | project_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_project table. |
Reporter account ID*
The unique identifier of the person who reported the issue.
SQL name | reporter_account_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the account table in the schema for organization data. |
Resolution*
The current issue resolution.
SQL name | resolution |
|---|---|
Data type | String |
Example values | Done, Won’t do, Duplicate |
Resolution at
The date and time (UTC time zone) when the issue was resolved.
SQL name | resolution_at |
|---|---|
Data type | Datetime |
Resolution sort order
The sequence order of the resolution, matching its order of appearance in the Jira product.
SQL name | resolution_sort_order |
|---|---|
Data type | Number |
Row refreshed at
The last refresh time (UTC time zone) for this record.
SQL name | max_row_refreshed_at |
|---|---|
Data type | Datetime |
Status*
The current issue status.
SQL name | status |
|---|---|
Data type | String |
Example value | Code review |
Status category*
The status category of the current issue status.
SQL name | status_category |
|---|---|
Data type | String |
Example values | To do, In progress, Done |
Status sort order
The sequence order of the status, matching its order of appearance in the Jira product.
SQL name | status_sort_order |
|---|---|
Data type | Number |
Story points*
Only holds data for issues in team-managed projects. To get story points for issues in company-managed projects, use the “Issue field” table.
The number of story points assigned to the issue.
This field is populated with either the value in the “Story point estimate” field or the “Story Points” field (as long as the default configuration is unchanged), but it will prioritize the value in the “Story point estimate” field if both fields are used on the same issue.
SQL name | story_points |
|---|---|
Data type | Number |
Summary*
The summary of the issue.
SQL name | summary |
|---|---|
Data type | String |
Team ID
The unique identifier of the team associated with the issue.
SQL name | team_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the atlassian_team table in the schema for organization data. |
Time estimate (seconds)*
This column requires time-tracking enabled.
The estimated time (in seconds) remaining from the original estimate.
SQL name | time_estimate_secs |
|---|---|
Data type | Number |
Time spent (seconds)*
This column requires time-tracking enabled.
The amount of logged work (in seconds). The field is NULL if there is no time spent.
SQL name | time_spent_secs |
|---|---|
Data type | Number |
Updated at
The date and time (UTC time zone) when the issue was last updated.
SQL name | updated_at |
|---|---|
Data type | Datetime |
URL*
The URL of the issue.
SQL name | url |
|---|---|
Data type | String |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Issue affected version mapping
The jira_issue_affected_version_mapping table holds associations between issues and project versions in which a bug or problem was found.
The table uses the following columns:
Issue ID
The unique identifier of an issue.
SQL name | issue_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue table. |
Row refreshed at
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
|---|---|
Data type | Datetime |
Version ID
The unique identifier of a project version, which is the fix version of the issue.
SQL name | version_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_project_fix_version table. |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Issue cycle time
The jira_issue_cycle_time table contains data derived from the issue_status_history.
You can use it to calculate flow metrics based on issue status transitions. The start of work is defined as the first transition to a status assigned the In progress status category, and the end of work is defined as the latest transition to a status assigned the Done status category.
If the issue is currently in a status of the Done status category, the elapsed time will be available in the cycle_time_secs column. That value will be NULL if the end of work precedes the start of work, to avoid capturing negative cycle times).
The table uses the following columns:
Cycle time (seconds)
The overall time elapsed (in seconds) from the start of work to the end of work, indicating the overall issue cycle time.
If the recorded work_ended_at is before work_started_at, the value will be NULL.
SQL name | cycle_time_secs |
|---|---|
Data type | Number |
Issue ID
The unique identifier of the issue.
SQL name | issue_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue table. |
Work ended at
The latest date and time (UTC time zone) in which the issue transitioned into a status in the Done status category, denoting the time work on the issue completed.
SQL name | work_ended_at |
|---|---|
Data type | Datetime |
Work started at
The earliest date and time (UTC time zone) in which the issue transitioned into a status in the In progress status category, denoting the time work on the issue started.
SQL name | work_started_at |
|---|---|
Data type | Datetime |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Issue custom fields
You can only use this table in visual mode queries, and it’s not available for controls created without the control editor.
This table is derived from “Jira issue field” table and displays all your custom fields as regular columns that you can add to your query.
If you need other attributes for your custom fields, you’ll need to use the “Issue field” table.
Issue group mapping
The jira_issue_group_mapping table holds associations between issues and groups.
The table uses the following columns:
Field ID
The unique identifier of the issue field where the group was chosen.
SQL name | field_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue_field table. |
Group ID
The unique identifier of the group associated with the issue.
SQL name | group_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the atlassian_group table in the schema for organization data. |
Issue ID
The unique identifier of the issue.
SQL name | issue_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue table. |
Row refreshed at
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
|---|---|
Data type | Datetime |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Issue field
The jira_issue_field lists the contents of Jira fields.
The table uses the following columns:
Field ID
The unique identifier of the Jira field. Use it together with the issue_id column in this table as the primary key for issue field data.
SQL name | field_id |
|---|---|
Data type | String |
Field key*
The key of a Jira field.
The key can hold a different value to the ID when it is defined in apps.
SQL name | field_key |
|---|---|
Data type | String |
Field reference
The identifier of the field within a Jira site.
It can be used to associate this record with the field identifier in Jira’s REST APIs and URLs, but does not guarantee uniqueness across multiple workspaces.
SQL name | field_ref |
|---|---|
Data type | String |
Field type*
The data type stored in the field.
SQL name | field_type |
|---|---|
Data type | String |
Issue ID
The unique identifier of the issue. Use it together with the field_id column in this table as the primary key for issue field data.
SQL name | issue_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue table. |
Name*
The name of the field.
If a field contains multiple values, there will be a record for each value in the field.
If a field contains cascading values, there will be a record for the parent value and a record for each child value. To get the parent value, append (parent) to the field name. To get the child values, append (child) to the field name.
For example, let’s say you have a Jira field named “Location”. For the field’s value, you must first select a country then select a region. The country is stored in the parent value, and the region is stored in the child value. To query both from the Data Lake, you’d filter for the field names Location (parent) and Location (child) to get the country and region values, respectively.
SQL name | name |
|---|---|
Data type | String |
Value*
The contents of the Jira field for this issue. The type of data represented by this string will depend on the field.
SQL name | value |
|---|---|
Data type | String |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Issue fix version mapping
The jira_issue_fix_version_mapping table holds associations between issues and project versions that are fix versions for an issue.
The table uses the following columns:
Issue ID
The unique identifier of an issue.
SQL name | issue_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue table. |
Row refreshed at
The last refresh time (UTC time zone) for this record.
SQL name | row_refreshed_at |
|---|---|
Data type | Datetime |
Version ID
The unique identifier of a project version, which is the fix version of the issue.
SQL name | version_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_project_fix_version table. |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Issue history
The jira_issue_history table tracks changes in issue data over time. Unlike the History tab in issue details, this table includes the initial value allocated to a field on issue creation.
Sometimes, several updates are applied to the issue at the same time. These updates will share the same changelog_id value. For each of these groups of changes, there will be a row for each field that was updated.
The table uses the following columns:
Author account ID*
The unique identifier of the person who applied the changes to the issue.
SQL name | author_account_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the account table in the schema for organization data. |
Changelog ID
The unique identifier for a group of updates to an issue.
SQL name | changelog_id |
|---|---|
Data type | String |
Ended at
The date and time (UTC time zone) when the new field value was replaced by another.
This is the time where the change captured in this record was displaced by a subsequent change. It is NULL if there was no subsequent change.
SQL name | ended_at |
|---|---|
Data type | Datetime |
Field*
The name of the field that was updated.
SQL name | field |
|---|---|
Data type | String |
Field ID
The unique identifier of the field that was updated.
SQL name | field_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue_field table. |
Field key*
The key of the field.
SQL name | field_key |
|---|---|
Data type | String |
Field name*
The current name of the field that was updated.
It can be NULL if the field was removed since the history record was created.
SQL name | field_name |
|---|---|
Data type | String |
Field type*
The type of the field that was updated.
It can be NULL if the field was removed since the history record was created.
SQL name | field_type |
|---|---|
Data type | String |
Issue ID
The unique identifier of the issue that was updated.
SQL name | issue_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue table. |
Previous value*
The value of the field before the change.
SQL name | prev_value |
|---|---|
Data type | String |
Previous value (string)*
A string representing the value of the field before the change.
SQL name | prev_value_string |
|---|---|
Data type | String |
Started at*
The date and time (UTC time zone) when the new field value became effective.
This is the time of the change captured by this record.
SQL name | started_at |
|---|---|
Data type | Datetime |
Time in value (seconds)
The time interval (in seconds) between the start and end time of this field value.
It is NULL if there was no subsequent change in the field (in other words, the new field value is the current one).
SQL name | time_in_value_secs |
|---|---|
Data type | Number |
Value*
The value of the field after the change.
SQL name | value |
|---|---|
Data type | String |
Value (string)*
A string representing the value of the field after the change.
SQL name | value_string |
|---|---|
Data type | String |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Issue status history
The jira_issue_status_history table tracks changes in issue status over time. Unlike the History tab in issue details, this tables also includes the initial status on issue creation. There is a row for each issue transition.
The table uses the following columns:
Author account ID*
The unique identifier of the person who set or changed the issue status.
SQL name | author_account_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the account table in the schema for organization data. |
Ended at
The date and time (UTC time zone) when the new issue status was replaced by another.
It is NULL if there was no subsequent issue transition.
SQL name | ended_at |
|---|---|
Data type | Datetime |
Issue ID
The unique identifier of the Jira issue that was updated.
SQL name | issue_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the jira_issue table. |
Previous state*
The previous issue status.
SQL name | prev_status |
|---|---|
Data type | String |
Previous status category*
The status category of the previous issue status.
SQL name | prev_status_category |
|---|---|
Data type | String |
Previous status ID
The unique identifier of the previous issue status.
SQL name | prev_status_id |
|---|---|
Data type | String |
Started at*
The date and time (UTC time zone) when the new issue status was set.
SQL name | started_at |
|---|---|
Data type | Datetime |
Status*
The new issue status.
SQL name | status |
|---|---|
Data type | String |
Status category*
The status category of the new issue status.
SQL name | status_category |
|---|---|
Data type | String |
Status ID
The unique identifier of the new issue status.
SQL name | status_id |
|---|---|
Data type | String |
Time in status (seconds)
The time interval (in seconds) between the start and end time of the status.
It will be NULL if there was no subsequent issue transition (in other words, the new issue status is the current one).
SQL name | time_in_status_secs |
|---|---|
Data type | Number |
Workspace ID
An Atlassian identifier that maps data to a site in the workspace table
SQL name | workspace_id |
|---|---|
Data type | String |
Foreign key | Links to a record in the workspace table in the schema for organization data. |
Was this helpful?