Create, modify, delete and query Issues in Jira.

Table Specific Information

You should set the IncludeCustomFields connection property to 'True' in order to dynamically retrieve the Custom Fields columns and values (like Sprint, Epic, ...) when querying this table.

You can search for Issues by using SQL and JQL (Jira Query Language).

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 provider uses the Jira API to process some of the filters. The provider processes other filters client-side within the provider. You can turn off the client-side execution of the query by setting SupportEnhancedSQL to false in which case any search criteria that refers to other columns will cause inconsistent data. For example, the following queries are processed server side.

SQL and JQL

SELECT * FROM Issues WHERE ProjectName = 'New project' AND JQL = ' status WAS "open" '

Filter time interval fields like TimeSpent, OriginalEstimate and RemainingEstimate by specifying hours, days, weeks, minutes and so on.

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 will cost 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 info of existing issues by providing the Id

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 that the object must first be created in the Jira application to use it with the provider queries. NOTE: The IssuelinksAggregate field has slightly different input requirements, outlined further below.

Structure for adding a fix version:

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

or

{ "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 NOTE: The set command will overwrite all the previous objects, meaning that previous entries will be discarded, and only the objects specified in the set command will be saved to the issue. The set command must be followed by an array:

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

or 

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

And an example query would be:

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"} }

or

{ "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"}
  }
}

or

{ "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'"

Delete

Delete an issue by providing the Id. Note that subtasks that have not been completed will be 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 Id of the issue.
Key	String	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	The key of the issue.
IssueTypeId	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The issue type Id.
IssueTypeName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The issue type name.
ProjectId	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The project Id of the issue.
ProjectName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The project name of the issue.
ProjectKey	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The project key of the issue.
ParentId	Int	False		=,!=,IN,NOT IN	Common	The Id of the issue's parent if the issue is a subtask.
ParentKey	String	True		=,!=,IN,NOT IN	Common	The key of the issue's parent if the issue is a subtask.
ParentIssueTypeId	String	True			Common	The issue type id of the issue's parent.
ParentIssueTypeName	String	True			Common	The issue type name of the issue's parent.
ParentStatusId	String	True			Common	The status id of the issue's parent.
ParentStatusName	String	True			Common	The status name of the issue's parent.
ResolutionId	String	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The resolution Id of the issue.
ResolutionName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The resolution name of the issue.
ResolutionDescription	String	True			Common	The resolution description of the issue.
ResolutionDate	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The resolution date of the issue.
Workratio	Long	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The work ratio of the issue.
LastViewed	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The last time that the issue was viewed.
WatchCount	Int	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	The number of watches of the issue.
IsWatching	Bool	True			Common	Whether the currently authenticated user is watching the issue.
Created	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The creation date of the issue.
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 priority name of the issue.
TimeSpentSeconds	Long	True			Common	The time spent in seconds on the issue.
TimeSpent	String	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The time spent on the issue.
RemainingEstimateSeconds	Long	True			Common	The time estimate in seconds of the issue.
RemainingEstimate	String	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The time estimate of the issue.
OriginalEstimateSeconds	Long	True			Common	The original time estimate in seconds of the issue.
OriginalEstimate	String	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The original time estimate of the issue.
AggregateTimeSpent	Long	True			Common	The aggregate time spent of the issue.
AggregateTimeOriginalEstimate	Long	True			Common	The original aggregate time estimate of the issue.
AggregateTimeEstimate	Long	True			Common	The aggregate time estimate of the issue.
AssigneeDisplayName	String	True			Common	Assignee display name.
AssigneeKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	[DEPRECATED] The assignee key of the issue.
AssigneeAccountId	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The assignee account id.
AssigneeName	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The assignee name of the issue.
AssigneeEmail	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The assignee email of the issue.
Updated	Datetime	True		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The updated date of the issue.
StatusId	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The status Id of the issue.
StatusName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The status name of the issue.
StatusCategoryId	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The status category id of the issue.
StatusCategoryKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The status category key of the issue.
StatusCategoryName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The status category name of the issue.
Description	String	False		IS,IS NOT	Common	The description of the issue.
Summary	String	False		IS,IS NOT	Common	The summary of the issue.
CreatorDisplayName	String	True			Common	Issue creator display name.
CreatorName	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The creator name of the issue.
CreatorAccountId	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The creator account id.
CreatorKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	[DEPRECATED] The creator key of the issue.
CreatorEmail	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The creator email of the issue.
ReporterDisplayName	String	True			Common	Issue reporter display name.
ReporterName	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The reporter name of the issue.
ReporterKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	[DEPRECATED]The reporter key of the issue.
ReporterAccountId	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The reporter account id.
ReporterEmail	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The reporter email of the issue.
AggregateProgress	Long	True			Common	The aggregate progress of the issue.
TotalProgress	Long	True			Common	The aggregate total progress of the issue.
Votes	Int	True		=,!=,>,>=,<,<=,IN,NOT IN	Common	Votes of the issue.
HasVotes	Bool	True			Common	The vote status of the issue.
DueDate	Date	False		=,!=,>,>=,<,<=,IS,IS NOT,IN,NOT IN	Common	The due date of the issue.
Labels	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The labels of an issue.
Environment	String	False		LIKE,NOT LIKE,IS,IS NOT	Common	The environment of an issue.
SecurityLevel	String	False		=,!=,IS,IS NOT,IN, NOT IN	Common	The security level of an issue.
FixVersionsAggregate	String	False			Common	The fix versions of the issue
ComponentsAggregate	String	False			Common	Aggregate list of components included in the issue.
IssueLinksAggregate	String	False			Common	The issue links of the issue.
AffectedVersionsAggregate	String	False			Common	The affected versions of the issue.
SubtasksAggregate	String	True			Common	The subtasks of the issue.
CustomField1	Any	False			Common	A custom field created for the Issues. The custom fields will be listed if the 'IncludeCustomFields' connection property is set to true.
CustomField2	Any	False			Common	A custom field created for the Issues. The custom fields will be listed if the 'IncludeCustomFields' connection property is set to true.

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name	Type	Description
JQL	String	JQL (Jira Query Language) allows you to build structured queries.
Attachments	String	Search for issues that have or do not have attachments. This column can be queried with IS EMPTY or IS NOT EMPTY.
Comment	String	Search for issues that have a comment that contains particular text.
Category	String	Search for issues that belong to projects in a particular category.
SprintId	Integer	Search for issues that belongs to a specific sprint, by specifying the Id of the Sprint.
SprintName	String	Search for issues that belongs to a specific sprint, by specifying the name of the Sprint.
Filter	String	Search for issues of saved filters. This column can be queried with filter name or filter id.