Data pipeline export schema

On this page

Still need help?

The Atlassian Community is here for you.

Ask the community

This page describes the structure and data schema of the Jira data export files.

To learn more about how to set up and configure your data pipeline, see Data pipeline.

The output files are written in CSV format and are RFC4180 compliant. They have the following characteristics:

  • Each file has a header. This includes files from exports that resulted in no data.
  • New lines are separated by CRLF characters \r\n.
  • Fields containing line breaks (CRLF), double quotes, and commas are enclosed in double quote.
  • If double-quotes are present inside fields, then a double-quote appearing inside a field are escaped by preceding it with another double quote. For example: "aaa", "b""bb", "ccc".
  • Fields with no data (null values) are represented in the CSV export by two consecutive delimiters (as in, ,,).
  • Embedded break lines are escaped by default and printed as n. 

On this page:

Issues schema 

All data relating to issues will be exported to issues_job<job_id>_<timestamp>.csv
FieldDescription
id (Primary Key)

Type: Number

Description: Unique ID of this issue. Use as Primary Key.

Example: 10002

instance_url

Type: String

Description: Base url of the current instance

Example: http://www.example.com/jira

url

Type: String

Description: URL of the issue

Example: http://www.example.com/jira/browse/TEST-1002

key

Type: String

Description: Unique Key for this issue

Example: TEST-1002

project_key

Type: String

Description: Key of the project this issue is in

Example: TEST

project_name

Type: String

Description: Title of the project this issue is in

Example: Sample Test Project

project_type

Type: String

Description: Type of the project this issue is in (can be Business, Software, Service Desk)

Example: Software

project_category

Type: String

Description: Assigned category for the project this issue is in

Example: Sample Project Category

issue_type

Type: String

Description: Type of issue (for example, task, bug, or epic)

Example: Task

summary

Type: String

Description: Summary of the issue

Example: Sample issue

description

Type: String

Description: Description of the issue (limited to 2000 characters)

Example: This is a sample issue for demo purposes.

creator_id

Type: Number

Description: Unique identifier of the user who created the issue, regardless of directory

Example: 10000

creator_name

Type: String

Description: Name of the user issue creator

Example: Alana Grant

reporter_id

Type: Number

Description: Unique identifier of the issue reporter, regardless of directory

Example: 10001

reporter_name

Type: String

Description: Name of the issue reporter

Example: Omar Darboe

assignee_id

Type: Number

Description: Unique identifier of the issue assignee, regardless of directory

Example: 10002

assignee_name

Type: String

Description: Name of the issue assignee

Example: Jie Yang Song

status

Type: String

Description: Current status of this issue

Example: Not Started

status_category

Type: String

Description: Status category for the current status for this issue

Example: In Progress

priority_sequence

Type: String

Description: The priority order (admins can change this which will result in non-deterministic priority orders)

Example: 3

priority_name

Type: String

Description: Name of the priority for this issue

Example: Medium

resolution

Type: String

Description: The final status of an issue. Jira considers the workflow of an Issue complete once a value is present in the resolution field.

Example: Done

watcher_count

Type: Number

Description: Number of users watching this issue

Example: 5

vote_count

Type: Number

Description: Number of votes for this issue

Example: 4

created_date

Type: Date

Description: UTC timezone creation date of this issue (ISO string)

Example: 2020-11-05T14:44:57Z

resolution_date

Type: Date

Description: UTC timezone resolution date of this issue (ISO string)

Example: 2020-11-05T14:44:57Z

updated_date

Type: Date

Description: UTC timezone date that this issue was last updated (ISO string)

Example: 2020-11-05T14:44:57Z

due_date

Type: Date

Description: UTC timezone date that this issue is due to be completed (ISO string)

Example: 2020-11-05T14:44:57Z

estimate

Type: Number

Description: Estimate time remaining from original estimate, in seconds (requires time-tracking enabled)

Example: 5000

original_estimate

Type: Number

Description: Original estimate that was set, in seconds (requires time-tracking enabled)

Example: 2000

time_spent

Type: Number

Description: Amount of logged work, in seconds (requires time-tracking enabled). Field is null if there is no time spent.

Example: 4000

labels

Type: String

Description: JSON array of label names. Field is null if there are no labels.

Example: ["test-label", "test2-label”]

components

Type: String

Description: JSON array of component names. Field is null if there are no labels.

Example: ["component_a", "component_b"]

parent_id

Type: Number

Description: ID of the parent issue. Field is null if the issue is not a subtask.

Example: 10001

environment

Type: String

Description: A short description of the environment in which the issue occurred (for example, IE9 on Windows 7). This field has a limit of 2000 characters

Example: "linux”

affected_versions

Type: String

Description: JSON Array of Affected Version Names. Field is null if there are no labels.

Example: ["a_version_1", "a_version_2"]

fix_versions

Type: String

Description: JSON array of fix version names. Field is null if there are no labels.

Example: ["f_version_1", "f_version_2"]

security_level

Type: String

Description: Security level name. NOTE: This can change between data exports.

Example: staff-only

Fields schema for Jira Software and Jira Service Management fields

Data from Jira Software and Jira Service Management fields will be exported to issue_fields_job<job_id>_<timestamp>.csv, and every custom field is displayed on a separate row. To map a custom field to its corresponding issue, use the issue_id from this file and find its matching ID from the Issues file. 

User-generated custom fields, and fields provided by apps are generally included, as long as they are an exportable type (fields that implement ExportableCustomFieldsType).

If a custom field contains no data, it will not be exported.

FieldDescription
issue_id

Type: Number

Description: ID to link to the corresponding issue in Issues data schema.

Example: 10002

field_id

Type: String

Description: Pre-defined IDs of the Jira Software and Jira Service Management fields.

Example: story_points

field_name

Type: String

Description: Name of the field

Example: Story Points

field_value

Type: Number, String, JSON array

Description: Value of the field

Examples: 4 / [{"sprintName": "A"}, {"sprintName": "B"}]/5000

The following Jira Software fields relate to non-epic issues.
sprint

Type: JSON

Description: Sprints that the issue is currently in or has been in. All date and times are formatted as an ISO string with UTC timezone.

Example:

[
  {
    "id": 2,
    "name": "Sample Sprint 1",
    "goal": "",
    "boardId": 1,
    "state": "FUTURE",
    "startDate": "2020-09-20T19:44:13Z",
    "endDate": "2020-10-04T18:44:13Z",
    "endDate": "2020-10-04T18:44:13Z"
  }
]
epic_link_id

Type: Number

Description: ID of the epic linked to this issue

Example: 10000

story_points

Type: Number

Description: Number of story points associated with this issue

Example: 5

The following Jira Software fields relate to epics
epic_color

Type: String

Description: Color of the epic

Example: ghx-label-3

epic_name

Type: String

Description: Name of the epic

Example: Test Epic

epic_status

Type: String

Description: Status of the epic

Example: To Do

The following fields relate to Jira Service Management
customer_request_type

Type: String

Description: Customer Request Type for a JSM Customer Request

Example: Request new software

organizations

Type: String

Description: List of organizations assigned to a given JSM Issue. Organizations are entities that admins can organize customers into.

Example: ["org1", "org2"]

request_participants

Type: Number

Description: List of users assigned as request participants for a given JSM Issue

Example:

[
 {
  "id": 10100,
  "name": "User 1 display name"
 },
 {
  "id": 10101,
  "name": "user 2 display name"
 }
]
satisfaction_comment

Type: String

Description: Comment given as part of a JSM Issue Satisfaction survey

Example: I got a prompt response. Thank you!

satisfaction_rating

Type: String

Description: Rating given as part of a JSM Issue Satisfaction survey

Example: 4.0

satisfaction_scale

Type: String

Description: Scale (max rating) the given satisfaction rating is based upon

Example: 5.0

satisfaction_date

Type: Date

Description: Date the Satisfaction survey was completed

Example: 2020-10-04T18:44:13Z

Issue history schema

The history of each issue will be exported to issue_history_job<job_id>_<timestamp>.csv. Only history after the export fromDate will be included.

Use the issue_id field to join this table to the Issues and Fields tables.

FieldDescription

issue_id (foreign key)

Type: Number

Description: Unique ID of this issue. Link to the corresponding issue in the issues table.

Example: 10002

changelog_id

Type: Number

Description: Identifier for the change, or group of changes that were made at the same time. Combine this with field to identify a single change.

Example: 10456

author_id

Type: Number

Description: Unique identifier for the user who made the change.

Example: 10000

author_key

Type: String

Description: Unique identifier of the author of the change as a unique string.

Example: JIRAUSER10000

created_date

Type: Date

Description: UTC timezone date of this change (ISO string), truncated to minutes.

Example: 2021-05-12T23:01:42Z

field

Type: String

Description: The name of the field that was changed.

Example: assignee

field_type

Type: Number

Description: The type of field that was changed, either jira or custom

Example: jira

from

Type: String

Description: Identifier for the value of the field before the change. Limited to 2000 characters.

Example: JIRAUSER10001

from_string

Type: String

Description: Value of the field before the change, as a string. Limited to 2000 characters.

Example: Alana Grant

to

Type: String

Description: Identifier for the value of the field after the change. Limited to 2000 characters.

Example: JIRAUSER10002

to_string

Type: String

Description: Value of the field after the change, as a string. Limited to 2000 characters.

Example: Omar Darboe

additional_information

Type: String

Description: Any additional information associated with the change, in JSON format.

Example:
{"old_status_category_id":"2",
"old_status_category_name":"New",
"new_status_category_id":"4",
"new_status_category_name":"In Progress"}

Note that exporting issue history can take some time, and there’s a small chance that an issue may be updated after the details for that issue have been fetched. This can lead to the updated date in the issues file being earlier than the last change in the issue history file.

Show me an example...

For example, DEV-456 is updated at 10:02:00, and again fifteen seconds later. If the data pipeline was fetching details of that issue at exactly that moment, the updated time in the issues file would be 10:02:00, but the issue history file would contain the subsequent change from 10:02:15.

SLA Cycle data schema (for Jira Service Management only)

Data relating to SLA Cycle fields from Jira Service Management will be exported to sla_cycles_job<job_id>_<timestamp>.csv. Each issue can have multiple SLAs, where each SLA cycle (Ongoing and/or Completed) is displayed on a separate row. To map an SLA to its corresponding issue, use the issue_id from this file and find its matching ID from the Issues file.
FieldDescription
issue_id

Type: Number

Description: ID to link to the corresponding issue in Issues data schema

Example: 10002

sla_id

Type: Number

Description: ID of the SLA of which the SLA cycle belongs to

Example: 15364

sla_name

Type: String

Description: Name of the SLA of which the cycle belongs to.

Example: Time to first response

cycle_type

Type: String

Description: Whether current SLA cycle is Ongoing or Completed

Example: Ongoing

start_time

Type: Date

Description: Timestamp of when the SLA cycle started

Example: 2020-01-10T12:50:30Z

stop_time

Type: Date

Description: Timestamp of when the SLA cycle transitioned from Ongoing to Completed. Only available for Ongoing cycles.

Example: 2020-01-10T12:50:30Z

paused

Type: Boolean

Description: Notes whether the SLA cycle is paused or not. Can be true, false, or empty. Only available for Ongoing cycles.

Example: True

remaining_time

Type: Number

Description: Represents the time (in milliseconds) remaining before the expected SLA limit is breached. Remaining times are calculated and updated every 30 minutes. Therefore, the outputted value may not represent the actual current remaining time.

Example: 14400000

elapsed_time

Type: Number

Description: Represents the time (in milliseconds) that has passed since the SLA cycle started.

Example: 14400000

goal_duration

Type: Number

Description: Represents time (in milliseconds) taken to complete the current cycle. Only available for Completed cycles.

Example: 14400000

Last modified on Jul 1, 2021

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.