Issues

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

テーブル固有の情報

このテーブルをクエリするときにカスタムフィールドのカラムと値（Sprint、Epic など）を動的に取得するためには、IncludeCustomFields 接続プロパティを'True' に設定する必要があります。

SQL とJQL (Jira Query Language) を使用して、Issues を検索できます。

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'

connector はJira API を使用して一部のフィルタを処理します。connector はクライアント側でconnector 内で他のフィルタを処理します。例えば、次のクエリはサーバー側で処理されます。

SQL およびJQL

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

時間、日、週、分などを指定して、TimeSpent、OriginalEstimate、RemainingEstimate などの時間間隔のフィールドをフィルタリングします。

SELECT * FROM Issues WHERE RemainingEstimate = '1m 2d' AND TimeSpent = '3m 2w'

Insert

プロジェクトで新しい課題を作成します。挿入には、Project（ProjectId またはProjectKey を指定）、IssueType（IssueTypeId またはIssueTypeName を指定）、およびSummary が必要です。ProjectName を指定することもできますが、ProjectKey が属する値を見つけるためにサーバーへの追加リクエストが発生します。Project やIssueType によっては、さらに多くのフィールドが必要な場合があります。

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

ID を入力して既存の課題の情報を更新します。

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

また、特定の課題の集計フィールドを変更することもできます。FixVersionsAggregate、AffectedVersionsAggregate、ComponentsAggregate、およびIssueLinksAggregate を変更できます。これらのカラムの特別な入力を使用して、課題の適切なフィールドを割り当て、削除、および更新できます。プロバイダークエリで使用するには、Jira アプリケーションで先にオブジェクトを作成しておく必要があります。NOTE：IssuelinksAggregate フィールドは入力の要件がわずかに異なります。以下で概要を説明します。

修正バージョンを追加する構造：

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

or

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

サンプルクエリ：

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

一度に追加できるオブジェクトは1つのみですが、set コマンドを使用すると、複数のオブジェクトを割り当てることができます。重要：set コマンドは、以前のすべてのオブジェクトを上書きします。つまり、以前のエントリは破棄され、 set コマンドで指定されたオブジェクトのみが課題に保存されます。set コマンドの後ろには配列が必要です。

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

or 

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

サンプルクエリは次のようになります。

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

remove コマンドを使用して、エントリを一度に1つずつ削除することもできます。

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

or

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

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

これらのコマンドは、新しい課題を挿入するときにも適用されます。

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

IssueLinksAggregate では'add' 操作のみがサポートされており、リンクのタイプおよびリンクの方向（'outwardIssue' か'inwardIssue' のいずれか1つ）を指定する必要があります。使用可能なリンクタイプはBlocks、RootCause、Relates、Duplicate、および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

ID を指定して課題を削除します。完了していないサブタスクも削除されることに注意してください。

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	If the current authenticated user is watching this issue, the value is 'true'; otherwise 'false'.
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 ID representing the priority level assigned to 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	Common	[DEPRECATED] The key identifier of the assignee. Use AssigneeAccountId instead.
AssigneeAccountId	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	The Atlassian account ID of the user assigned to the issue.
AssigneeName	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	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		IS,IS NOT	Common	A detailed explanation of the issue's problem, task, or request.
Summary	String	False		IS,IS NOT	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	Common	The username of the issue creator, typically used in legacy Jira systems.
CreatorAccountId	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	The Atlassian account ID of the user who created the issue.
CreatorKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	[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	Common	The username of the reporter, used in legacy Jira systems.
ReporterKey	String	True		=,!=,IS,IS NOT,IN,NOT IN	Common	[DEPRECATED] The key identifier for the reporter. Use ReporterAccountId instead.
ReporterAccountId	String	False		=,!=,IS,IS NOT,IN,NOT IN	Common	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,NOT LIKE,IS,IS NOT	Common	Describes the technical or physical environment relevant to the issue (such as OS, browser, or hardware).
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 will be 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.

Pseudo-Columns

SELECT ステートメントのWHERE 句では、疑似カラムフィールドを使用して、データソースから返されるタプルを詳細に制御することができます。


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 EMPTY or IS NOT EMPTY.
Comment	String	Filters issues that contain a comment with specified text content.
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.

Tableau Connector for Jira