Issues

Provides full access to Jira issues. Supports creation, updates, deletions, and queries for issue records across all projects.

Table-Specific Information

Set the IncludeCustomFields connection property to true to dynamically retrieve the Custom Fields columns and values (like Sprint, Epic, etc.) when querying this table.

You can query Issues by using a Jira Query Language (JQL) query as a filter.

SELECT

JQL

SELECT * FROM Issues WHERE JQL = ' project = "New project" AND Status WAS "open" ';

SQL

SELECT * FROM Issues WHERE ProjectId > 10001 OR Updated <= '2016/03/29 15:00';

The driver uses the Jira API to process filters by translating them to JQL. The driver processes all other filters client-side.

For example, the following queries are processed server-side:

SQL and JQL

SELECT * FROM Issues WHERE ProjectName = 'New project' AND JQL = ' status WAS "open" ';
SELECT * FROM Issues WHERE RemainingEstimate = '1m 2d' AND TimeSpent = '3m 2w';

INSERT

Create a new issue in the project. Project (specify either ProjectId or ProjectKey), IssueType (specify either IssueTypeId or IssueTypeName), and Summary are required for inserts. ProjectName may also be specified instead, but it costs an additional request to the server to find the ProjectKey value that it belongs to. Depending on the project and the IssueType, more fields may be required.

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, CustomField1) VALUES (10000, 'Desc from prod', 10100, 'test');
INSERT INTO Issues (ProjectKey, Summary, IssueTypeName, CustomField1) VALUES ('PROD', 'Desc from prod', 'Task', 'test');

UPDATE

Update an issue by specifying the Id and the columns to update.

UPDATE Issues SET Summary = 'Desc FROM prod', CustomField1 = 'ValidValue' WHERE Id = '10003';

You can also modify some aggregate fields for a specific issue. You can modify FixVersionsAggregate, AffectedVersionsAggregate, ComponentsAggregate, and IssueLinksAggregate. Using the special inputs for these columns, you can assign, remove, and update the appropriate field for an issue.

NOTE: The object must first be created in the Jira application to use it with the driver queries. The IssueLinksAggregate field has slightly different input requirements, outlined further below.

Structure for adding a fix version:

{ "add": {"name":"example"} }

{ "add": {"id":"1234"} }

An example query:

UPDATE Issues SET FixVersionsAggregate = '{"add":{"name":"extra"}}' WHERE Id = 19683;

You can only add one object at a time, but using the set command you can assign multiple objects.

IMPORTANT: The set command overwrites all the previous objects, meaning that previous entries are discarded, and only the objects specified in the set command are saved to the issue. The set command must be followed by an array:

{ "set":  [ {"name":"New Component 1"}, {"name":"Old Component 2"} ] }

{ "set":  [ {"id":"1234"}, {"name":"Old Component 2"} ] }

An example query:

UPDATE Issues SET ComponentsAggregate = '{ "set":  [ {"id":"1234"}, {"name":"Old Components 2"} ] }' WHERE Id = 19683;

You can also remove an entry one at a time using the remove command:

{ "remove": {"name":"example"} }

{ "remove": {"id":"1234"} }

UPDATE Issues SET AffectedVersionsAggregate = '{"remove":{"name":"extra"}}' WHERE Id = 19683;

These commands are also applicable when inserting a new issue:

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, FixVersionsAggregate) VALUES (10000, 'Desc from prod', 10100, '{"add":{"name":"Example1"}}');

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, FixVersionsAggregate) VALUES (10000, 'Desc from prod', 10100, '{"set":[{"name":"Example1"}]}');

IssueLinksAggregate

Only the 'add' operation is supported with IssueLinksAggregate, and you must specify the type of the link and the direction of the link, either 'outwardIssue' or 'inwardIssue', but not both. The valid link types are Blocks, RootCause, Relates, Duplicate, and Cloners.

{ "add":
  { "outwardIssue":
      {"key":"COB-15"},
    "type":
      {"name":"Cloners"}
  }
}

{ "add":
  { "inwardIssue":
    {"key":"FIL-78"},
    "type":
    {"name":"Blocks"}
  }
}

INSERT INTO Issues (ProjectId, Summary, IssueTypeId, FixVersionsAggregate) VALUES (10000, 'Desc from prod', 10100, '{"add":{"outwardIssue":{"key":"COB-15"},"type":{"name":"Cloners"}}}');

UPDATE Issues SET IssueLinksAggregate = '{"add":{"inwardIssue":{"key":"FIL-1"},"type":{"name":"Blocks"}}}' WHERE Id = '22204';

ADF Fields (REST_V3)

When the APIVersion connection property is set to REST_V3, the Description_ADF column is available alongside Description. Use Description to insert or update a plain-text value, which the driver wraps into ADF format automatically. Use Description_ADF to supply raw ADF JSON directly.

Insert a new issue with a plain-text description (wrapped as ADF automatically):

INSERT INTO Issues (ProjectKey, Summary, IssueTypeName, Description) VALUES ('PROJ', 'My Issue', 'Task', 'Plain text description');

Insert a new issue with a raw ADF JSON description:

INSERT INTO Issues (ProjectKey, Summary, IssueTypeName, Description_ADF) VALUES ('PROJ', 'My Issue', 'Task', '{"type":"doc","version":1,"content":[{"type":"paragraph","content":[{"type":"text","text":"Raw ADF description"}]}]}');

Update the description of an issue using plain text:

UPDATE Issues SET Description = 'Updated description' WHERE Id = '10003';

Update the description using raw ADF JSON:

UPDATE Issues SET Description_ADF = '{"type":"doc","version":1,"content":[{"type":"paragraph","content":[{"type":"text","text":"Updated ADF description"}]}]}' WHERE Id = '10003';

NOTE: Specifying both Description and Description_ADF in the same statement results in an error. Use one or the other.

DELETE

Delete an issue by specifying the Id. Subtasks that have not been completed are deleted as well.

DELETE FROM Issues WHERE Id = '10003';

Columns


Name	Type	ReadOnly	References	SupportedOperators	Platform	Description
Id [KEY]	Int	True		=,!=,IN,NOT IN	Common	The unique numeric identifier assigned to the issue by Jira.
Key	String	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	The unique key used to reference the issue, typically in the format 'PROJ-123'.
IssueTypeId	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The internal identifier of the issue type, such as 'Bug', 'Task', or 'Story'.
IssueTypeName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The human-readable name of the issue type, such as 'Bug' or 'Task'.
ProjectId	Integer	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The unique identifier of the project that contains the issue.
ProjectName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The name of the project that the issue belongs to.
ProjectKey	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The unique key of the project, often used as the prefix in issue keys (for example, 'PROJ').
ParentId	Int	False		=,!=,IN,NOT IN	Common	The ID of the parent issue, used when the issue is a subtask.
ParentKey	String	True		=,!=,IN,NOT IN	Common	The key of the parent issue, used when the issue is a subtask.
ParentIssueTypeId	String	True			Common	The issue type ID of the parent issue, providing insight into the parent issue's classification.
ParentIssueTypeName	String	True			Common	The issue type name of the parent issue, such as 'Story' or 'Task'.
ParentStatusId	String	True			Common	The status ID of the parent issue, indicating the workflow status.
ParentStatusName	String	True			Common	The status name of the parent issue, such as 'In Progress' or 'Done'.
ResolutionId	String	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The resolution ID of the issue, representing how the issue was resolved.
ResolutionName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The human-readable name of the issue's resolution, such as 'Fixed' or 'Duplicate'.
ResolutionDescription	String	True			Common	A detailed explanation of the resolution applied to the issue.
ResolutionDate	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date and time the issue was marked as resolved.
Workratio	Long	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	A percentage value comparing the amount of time spent versus the estimated effort.
LastViewed	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The date and time the issue was last viewed by the current user.
WatchCount	Int	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	The number of users who are watching the issue for updates.
IsWatching	Bool	True			Common	Indicates whether the current authenticated user is watching this issue.
Created	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The timestamp when the issue was initially created in Jira.
PriorityId	String	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The priority ID of the issue.
PriorityName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The name of the priority level, such as 'High', 'Medium', or 'Low'.
TimeSpentSeconds	Long	True			Common	The total time spent working on the issue, measured in seconds.
TimeSpent	String	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The total time spent on the issue, formatted as a human-readable string (for example, '2h 30m').
RemainingEstimateSeconds	Long	True			Common	The remaining estimated effort required to complete the issue, in seconds.
RemainingEstimate	String	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The remaining time estimate formatted as a string (for example, '1d 2h').
OriginalEstimateSeconds	Long	True			Common	The original estimated time required to complete the issue, in seconds.
OriginalEstimate	String	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The original time estimate displayed as a formatted string (for example, '3d').
AggregateTimeSpent	Long	True			Common	The total time spent across the issue and its subtasks, in seconds.
AggregateTimeOriginalEstimate	Long	True			Common	The total of the original time estimates for the issue and all its subtasks.
AggregateTimeEstimate	Long	True			Common	The current total remaining estimate for the issue and all of its subtasks.
AssigneeDisplayName	String	True			Common	The full display name of the user assigned to the issue.
AssigneeKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Server	[DEPRECATED] The key identifier of the assignee. Use AssigneeAccountId instead.
AssigneeAccountId	String	False	Users.AccountId	=,!=,IS,IS NOT,IN,NOT IN	Cloud	The Atlassian account ID of the user assigned to the issue.
AssigneeName	String	False		=,!=,IS,IS NOT,IN,NOT IN	Server	The username of the assignee, used in older Jira instances.
AssigneeEmail	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The email address of the user assigned to the issue.
Updated	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The timestamp of the last update made to the issue.
StatusId	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The internal identifier of the issue's current workflow status.
StatusName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The name of the issue's status, such as 'To Do', 'In Progress', or 'Done'.
StatusCategoryId	Integer	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The category ID associated with the issue status, used to group similar workflow statuses.
StatusCategoryKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The key used to identify the status category, such as 'new', 'indeterminate', or 'done'.
StatusCategoryName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The display name of the status category, used to group workflow states.
Description	String	False		=,LIKE,IS,IS NOT,EMP_EQ	Common	A detailed explanation of the issue's problem, task, or request.
Description_ADF	String	False			Cloud	Raw Atlassian Document Format (ADF) JSON value of 'Description'. Available only when the APIVersion connection property is set to REST_V3.
Summary	String	False		=,LIKE,IS,IS NOT,EMP_EQ	Common	A brief summary or title that provides an overview of the issue.
CreatorDisplayName	String	True			Common	The full display name of the user who created the issue.
CreatorName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Server	The username of the issue creator, typically used in legacy Jira systems.
CreatorAccountId	String	True	Users.AccountId	=,!=,IS,IS NOT,IN,NOT IN	Cloud	The Atlassian account ID of the user who created the issue.
CreatorKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Server	[DEPRECATED] The key identifier for the issue creator. Use CreatorAccountId instead.
CreatorEmail	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The email address of the user who created the issue.
ReporterDisplayName	String	True			Common	The full display name of the user who reported the issue.
ReporterName	String	False		=,!=,IS,IS NOT,IN,NOT IN	Server	The username of the reporter, used in legacy Jira systems.
ReporterKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Server	[DEPRECATED] The key identifier for the reporter. Use ReporterAccountId instead.
ReporterAccountId	String	False	Users.AccountId	=,!=,IS,IS NOT,IN,NOT IN	Cloud	The Atlassian account ID of the user who reported the issue.
ReporterEmail	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The email address of the user who reported the issue.
AggregateProgress	Long	True			Common	The total progress made on the issue, including progress on all subtasks, measured in seconds.
TotalProgress	Long	True			Common	The total estimated time for the issue and all its subtasks.
Votes	Int	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	The number of users who have voted for this issue as being important or needing attention.
HasVotes	Bool	True			Common	Indicates whether the issue has any user votes. Returns 'true' if votes exist, otherwise 'false'.
DueDate	Date	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The due date by which the issue is expected to be completed.
Labels	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	A comma-separated list of labels applied to the issue for categorization or filtering.
Environment	String	False		=,LIKE,IS,IS NOT,EMP_EQ	Common	Describes the technical or physical environment relevant to the issue (such as OS, browser, or hardware).
Environment_ADF	String	False			Cloud	Raw Atlassian Document Format (ADF) JSON value of 'Environment'. Available only when the APIVersion connection property is set to REST_V3.
SecurityLevel	String	False		=,!=,IS,IS NOT,IN, NOT IN	Common	The security level applied to the issue, restricting visibility to a defined user group.
FixVersionsAggregate	String	False			Common	A comma-separated list of versions in which the issue is or is resolved.
ComponentsAggregate	String	False			Common	A list of project components that this issue is associated with.
IssueLinksAggregate	String	False			Common	A list of links to other issues that relate to or are dependent on this issue.
AffectedVersionsAggregate	String	False			Common	A list of versions affected by this issue.
SubtasksAggregate	String	True			Common	A collection of subtasks associated with the issue.
CustomField1	Any	False			Common	A user-defined custom field for the issue. Custom fields appear when 'IncludeCustomFields' is enabled.
CustomField2	Any	False			Common	Another user-defined custom field available when 'IncludeCustomFields' is set to 'true'.
ItemURL	String	True			Common	A browsable URL linking directly to the issue in the Jira web interface.

Pseudocolumns

Pseudocolumn fields are used in the WHERE clause of SELECT statements and offer more granular control over the data returned from the data source.


Name	Type	Description
JQL	String	Used to filter and search issues using Jira Query Language (JQL).
Attachments	String	Used to query issues that have attachments. Can be filtered using IS NULL or IS NOT NULL.
Comments	String	Search for issues containing specific comment text. On Jira Cloud, the LIKE operator supports only the StartsWith pattern. On Server, it also supports EndsWith and Contains.
Category	String	Allows filtering of issues based on the category of the associated project.
SprintId	Integer	Filters issues by the numeric ID of the sprint they belong to.
SprintName	String	Filters issues by the name of the sprint they are part of.
Filter	String	Queries issues using the name or ID of a saved filter.

JDBC Driver for Jira