Reference

Endpoint Read Issues

Name

get_issues

Description

No description available

Related Tables

Issues

Parameters

Parameter

Required

Options

Name: Fields

Label: Fields

Option	Value
Default
Only Keys	id,key
All fields	*all

Name: SearchBy

Label: Search by

Option	Value
None
Project Key(s)	Project
JQL Expression	JQL
Issue Id(s) or Key(s)	Key

Name: Project

Label: Project Key(s) - Comma Separated (CBS,BRK)

Specify a project to search issues for

Name: JQL

Label: JQL Expression (For Issue Search)

Use JQL to use an advanced mode of searching. Few links are https://www.atlassian.com/blog/jira/jql-the-most-flexible-way-to-search-jira-14 and als check https://wac-cdn.atlassian.com/dam/jcr:241d9c9b-475f-411f-90a2-97ca344442b3/atlassian-jql-cheat-sheet-2.pdf?cdnVersion=2169

Option	Value
Search Ids (IN)	key IN(10001, 10002, 10003)
Search Keys (IN)	key IN(CS-1, CS-2, CS-3)
Search Projects (IN)	project IN(PROJ1, PROJ2, PROJ3)
Search Status (EQUAL)	status='Done'
Search Date (Expression 1)	created >=-5d
Search Date (Expression 2)	created >=startOfMonth() AND created <=now()
Search Date (Expression 3)	created >=startOfYear() AND created <=startOfDay()
Search Date (Expression 4 - Site Timezone)	created >= '2008-12-31 23:59'
Search Date (Issue Created After Date - Quote1)	created >= '2008-12-31'
Search Date (Issue Created After Date - Quote2)	created >= "2008-12-31"
Search Date (Issue Created After Date+Time - Site Timezone)	created >= '2008-12-31 23:59'
Search Date (Issue Updated After Date+Time - Site Timezone)	updated >= "2008-12-31 23:59"
Search Date (Issue Updated In Last 5 Days)	updated >=-5d
Search Date (Issue Updated In Last 24 Hours)	updated >=-24h
Search Date (Worklog Created or Updated After Date)	worklogDate >= '2008-12-31'
Search Date (Worklog Created or Updated In Last 2 Days)	worklogDate >= -2d
Search Date (Worklog Created or Updated After for Specified Projects)	project IN (PROJ1, PROJ2) AND worklogDate >= '2008-12-31'
Search Project and Status (AND + IN)	project=CS AND status NOT IN ('Done', 'Open', 'Closed')
Search Assignee and Created Date	assignee is NOT EMPTY and created < -1d
Search Text (Contains - Fuzzy)	Summary ~ 'some words' OR description ~ 'some words'
Search Text (Contains - Fuzzy Wildcard)	Summary ~ 'some' OR description ~ 'some'
Search Text (Contains - Exact)	Summary ~ '\"exact words\"' OR description ~ '\"exact words\"'
Search Text (Does Not Contain - Fuzzy)	Summary !~ 'some words' OR description !~ 'some words'
Search Empty OR Null	fixVersion is empty OR fixVersion is null
Search Is Not Empty OR Is Not Null	fixVersion is not empty OR fixVersion is not null
Search WAS Operator (previous value)	status WAS "Resolved" BY (jsmith,srogen) BEFORE "2019/02/02"
Search WAS IN Operator	status WAS IN ("Resolved", "In Progress")
Search WAS NOT IN Operator	status WAS NOT IN ("Resolved", "In Progress")
Search WAS + BY + DURING (date range)	status WAS "Resolved" BY (jsmith,srogen) DURING("2019/02/02", "2020/02/02")
Search CHANGED operator	assignee CHANGED
Search CHANGED operator (multiple)	status CHANGED FROM "In Progress" TO "Open"

Name: Key

Label: Issue Key(s) or Ids - Comma Separated (CBS-10,PRA-13)

Use comma separated IDs or Keys for Issues. 1000 ids max or JIRA internal limit for Payload.

Name: CustomColumnsRegex

Label: CustomColumnsRegex

Enter regular expression for custom columns which you like to output. Use Pipe to type multiple column names (must use key - internal names for columns e.g. customfield_10004). You can use Regex too (e.g. field_100\d+ ). This filter is only applied for those fields which has flag custom=true.

Name: MetaDetectionOrder

Label: Metadata Mode (Change if columns missing) **SLOW**

Only change if expected columns are missing (e.g. SLA or nested custom fields). Default uses predefined columns (fast). MergeStaticDynamic scans sample data to infer types and merge with static metadata—slower but exposes nested/document columns; use META='@OverrideMode:1' in SQL to add specific dynamic fields.

Option	Value
Default	Default
Static	Static
MergeStaticDynamic	MergeStaticDynamic

Output Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Description
Id	`DT_I8`	`bigint`
Key	`DT_WSTR`	`nvarchar(50)`	50
ProjectKey	`DT_WSTR`	`nvarchar(50)`	50
ProjectName	`DT_WSTR`	`nvarchar(500)`	500
StatusName	`DT_WSTR`	`nvarchar(500)`	500
Summary	`DT_WSTR`	`nvarchar(2000)`	2000
Created	`DT_DBTIMESTAMP`	`datetime`
Updated	`DT_DBTIMESTAMP`	`datetime`
Description	`DT_NTEXT`	`nvarchar(MAX)`		Join formatted text blocks using space separator (new lines will be lost)
DescriptionFormatted	`DT_NTEXT`	`nvarchar(MAX)`
CreatorAccountId	`DT_WSTR`	`nvarchar(100)`	100
CreatorEmailAddress	`DT_WSTR`	`nvarchar(100)`	100
ReporterAccountId	`DT_WSTR`	`nvarchar(100)`	100
ReporterEmailAddress	`DT_WSTR`	`nvarchar(100)`	100
AssigneeAccountId	`DT_WSTR`	`nvarchar(100)`	100
AssigneeEmailAddress	`DT_WSTR`	`nvarchar(100)`	100
ResolutionId	`DT_I8`	`bigint`
ResolutionDescription	`DT_WSTR`	`nvarchar(4000)`	4000
ResolutionName	`DT_WSTR`	`nvarchar(500)`	500
Resolution	`DT_NTEXT`	`nvarchar(MAX)`
TimespentInSeconds	`DT_I8`	`bigint`
DueDate	`DT_DBDATE`	`date`
Expand	`DT_WSTR`	`nvarchar(150)`	150
StatusCategoryChangeDate	`DT_DBTIMESTAMP`	`datetime`
Self	`DT_WSTR`	`nvarchar(150)`	150
IssueTypeSelf	`DT_WSTR`	`nvarchar(150)`	150
IssueTypeId	`DT_I8`	`bigint`
IssueTypeDescription	`DT_NTEXT`	`nvarchar(MAX)`
IssueTypeIconUrl	`DT_WSTR`	`nvarchar(500)`	500
IssueTypeName	`DT_WSTR`	`nvarchar(100)`	100
IssueTypeSubtask	`DT_BOOL`	`bit`
IssueTypeAvatarId	`DT_I4`	`int`
IssueTypeEntityId	`DT_GUID`	`uniqueidentifier`
IssueTypeHierarchyLevel	`DT_I4`	`int`
ProjectSelf	`DT_WSTR`	`nvarchar(150)`	150
ProjectId	`DT_I8`	`bigint`
ProjectTypeKey	`DT_WSTR`	`nvarchar(100)`	100
ProjectSimplified	`DT_BOOL`	`bit`
ProjectAvatarUrls48x48	`DT_WSTR`	`nvarchar(500)`	500
ProjectAvatarUrls24x24	`DT_WSTR`	`nvarchar(500)`	500
ProjectAvatarUrls16x16	`DT_WSTR`	`nvarchar(500)`	500
ProjectAvatarUrls32x32	`DT_WSTR`	`nvarchar(500)`	500
FixVersions	`DT_NTEXT`	`nvarchar(MAX)`
AggregateTimespentInSeconds	`DT_I8`	`bigint`
ResolutionDate	`DT_DBTIMESTAMP`	`datetime`
WorkRatio	`DT_WSTR`	`nvarchar(100)`	100
WatchesSelf	`DT_WSTR`	`nvarchar(150)`	150
WatchesWatchCount	`DT_I4`	`int`
WatchesIsWatching	`DT_BOOL`	`bit`
LastViewed	`DT_DBTIMESTAMP`	`datetime`
PrioritySelf	`DT_WSTR`	`nvarchar(150)`	150
PriorityIconUrl	`DT_WSTR`	`nvarchar(500)`	500
PriorityName	`DT_WSTR`	`nvarchar(100)`	100
PriorityId	`DT_I4`	`int`
Labels	`DT_NTEXT`	`nvarchar(MAX)`
AggregateTimeOriginalEstimate	`DT_WSTR`	`nvarchar(50)`	50
TimeEstimate	`DT_WSTR`	`nvarchar(50)`	50
Versions	`DT_NTEXT`	`nvarchar(MAX)`
IssueLinks	`DT_NTEXT`	`nvarchar(MAX)`
AssigneeSelf	`DT_WSTR`	`nvarchar(150)`	150
AssigneeAvatarUrls48x48	`DT_WSTR`	`nvarchar(500)`	500
AssigneeAvatarUrls24x24	`DT_WSTR`	`nvarchar(500)`	500
AssigneeAvatarUrls16x16	`DT_WSTR`	`nvarchar(500)`	500
AssigneeAvatarUrls32x32	`DT_WSTR`	`nvarchar(500)`	500
AssigneeDisplayName	`DT_WSTR`	`nvarchar(500)`	500
AssigneeActive	`DT_BOOL`	`bit`	32
AssigneeTimeZone	`DT_WSTR`	`nvarchar(100)`	100
AssigneeAccountType	`DT_WSTR`	`nvarchar(100)`	100
StatusSelf	`DT_WSTR`	`nvarchar(150)`	150
StatusDescription	`DT_NTEXT`	`nvarchar(MAX)`
StatusIconUrl	`DT_WSTR`	`nvarchar(500)`	500
StatusId	`DT_I4`	`int`
StatusCategorySelf	`DT_WSTR`	`nvarchar(150)`	150
StatusCategoryId	`DT_I4`	`int`
StatusCategoryKey	`DT_WSTR`	`nvarchar(100)`	100
StatusCategoryColorName	`DT_WSTR`	`nvarchar(100)`	100
StatusCategoryName	`DT_WSTR`	`nvarchar(100)`	100
Components	`DT_NTEXT`	`nvarchar(MAX)`
TimeOriginalEstimate	`DT_WSTR`	`nvarchar(50)`	50
DescriptionVersion	`DT_I4`	`int`
DescriptionType	`DT_WSTR`	`nvarchar(50)`	50
Security	`DT_WSTR`	`nvarchar(150)`	150
AggregateTimeEstimate	`DT_I8`	`bigint`
CreatorSelf	`DT_WSTR`	`nvarchar(150)`	150
CreatorAvatarUrls48x48	`DT_WSTR`	`nvarchar(500)`	500
CreatorAvatarUrls24x24	`DT_WSTR`	`nvarchar(500)`	500
CreatorAvatarUrls16x16	`DT_WSTR`	`nvarchar(500)`	500
CreatorAvatarUrls32x32	`DT_WSTR`	`nvarchar(500)`	500
CreatorDisplayName	`DT_WSTR`	`nvarchar(200)`	200
CreatorActive	`DT_BOOL`	`bit`
CreatorTimeZone	`DT_WSTR`	`nvarchar(100)`	100
CreatorAccountType	`DT_WSTR`	`nvarchar(100)`	100
Subtasks	`DT_NTEXT`	`nvarchar(MAX)`
ReporterSelf	`DT_WSTR`	`nvarchar(150)`	150
ReporterAvatarUrls48x48	`DT_WSTR`	`nvarchar(500)`	500
ReporterAvatarUrls24x24	`DT_WSTR`	`nvarchar(500)`	500
ReporterAvatarUrls16x16	`DT_WSTR`	`nvarchar(500)`	500
ReporterAvatarUrls32x32	`DT_WSTR`	`nvarchar(500)`	500
ReporterDisplayName	`DT_WSTR`	`nvarchar(100)`	100
ReporterActive	`DT_BOOL`	`bit`
ReporterTimeZone	`DT_WSTR`	`nvarchar(100)`	100
ReporterAccountType	`DT_WSTR`	`nvarchar(100)`	100
AggregateProgressPercent	`DT_I4`	`int`
AggregateProgressInSeconds	`DT_I8`	`bigint`
AggregateProgressTotalInSeconds	`DT_I8`	`bigint`
Environment	`DT_NTEXT`	`nvarchar(MAX)`		Join formatted text blocks using space separator (new lines will be lost)
EnvironmentFormatted	`DT_NTEXT`	`nvarchar(MAX)`
ProgressInSeconds	`DT_I8`	`bigint`
ProgressTotalInSeconds	`DT_I8`	`bigint`
VotesSelf	`DT_WSTR`	`nvarchar(150)`	150
Votes	`DT_I4`	`int`
HasVoted	`DT_BOOL`	`bit`
ParentId	`DT_I4`	`int`
ParentKey	`DT_WSTR`	`nvarchar(50)`	50
ParentSelf	`DT_WSTR`	`nvarchar(150)`	150
ParentSummary	`DT_WSTR`	`nvarchar(2000)`	2000
ParentStatusSelf	`DT_WSTR`	`nvarchar(150)`	150
ParentStatusDescription	`DT_WSTR`	`nvarchar(2000)`	2000
ParentStatusIconUrl	`DT_WSTR`	`nvarchar(500)`	500
ParentStatusName	`DT_WSTR`	`nvarchar(100)`	100
ParentStatusId	`DT_I8`	`bigint`
ParentStatusCategorySelf	`DT_WSTR`	`nvarchar(150)`	150
ParentStatusCategoryId	`DT_I4`	`int`
ParentStatusCategoryKey	`DT_WSTR`	`nvarchar(50)`	50
ParentStatusCategoryColorName	`DT_WSTR`	`nvarchar(50)`	50
ParentStatusCategoryName	`DT_WSTR`	`nvarchar(50)`	50
ParentprioritySelf	`DT_WSTR`	`nvarchar(150)`	150
ParentpriorityIconUrl	`DT_WSTR`	`nvarchar(500)`	500
ParentpriorityName	`DT_WSTR`	`nvarchar(50)`	50
ParentpriorityId	`DT_I4`	`int`
ParentIssueTypeSelf	`DT_WSTR`	`nvarchar(150)`	150
ParentIssueTypeId	`DT_I8`	`bigint`
ParentIssueTypeDescription	`DT_WSTR`	`nvarchar(2000)`	2000
ParentIssueTypeIconUrl	`DT_WSTR`	`nvarchar(500)`	500
ParentIssueTypeName	`DT_WSTR`	`nvarchar(50)`	50
ParentIssueTypeSubtask	`DT_BOOL`	`bit`
ParentIssueTypeAvatarId	`DT_I4`	`int`
ParentIssueTypeEntityId	`DT_GUID`	`uniqueidentifier`
ParentIssueTypeHierarchyLevel	`DT_I4`	`int`
Assignee	`DT_WSTR`	`nvarchar(150)`	150
ResolutionSelf	`DT_WSTR`	`nvarchar(150)`	150
[$parent.key$]	`DT_WSTR`	`nvarchar(2000)`	2000	[Dynamic Column]
[$parent.key$]_value	`DT_WSTR`	`nvarchar(2000)`	2000	[Dynamic Column]_value
[$parent.key$]_id	`DT_WSTR`	`nvarchar(20)`	20	[Dynamic Column]_id
[$parent.key$]_child_value	`DT_WSTR`	`nvarchar(2000)`	2000	[Dynamic Column]_child_value
[$parent.key$]_child_id	`DT_WSTR`	`nvarchar(20)`	20	[Dynamic Column]_child_id
[$parent.key$]_values	`DT_WSTR`	`nvarchar(2000)`	2000	[Dynamic Column]_values
[$parent.key$]_text	`DT_NTEXT`	`nvarchar(MAX)`		[Dynamic Column]_text Join formatted text blocks using space separator (new lines will be lost)
[$parent.key$]_accountId	`DT_WSTR`	`nvarchar(100)`	100	[Dynamic Column]_accountId
[$parent.key$]_email	`DT_WSTR`	`nvarchar(300)`	300	[Dynamic Column]_email
[$parent.key$]_name	`DT_WSTR`	`nvarchar(300)`	300	[Dynamic Column]_name

If the column you are looking for is missing, consider customizing Jira Connector.

Input Columns

Label	Data Type (SSIS)	Data Type (SQL)	Length	Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use Jira Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

This Endpoint belongs to the Issues table, therefore it is better to use it, instead of accessing the endpoint directly:

API Source - Jira

Read and write Jira data effortlessly. Track, manage, and automate issues, projects, worklogs, and comments — almost no coding required.

Jira

Issues

There are no parameters to configure.

SSIS API Source - Read from table or endpoint

API Destination

This Endpoint belongs to the Issues table, therefore it is better to use it, instead of accessing the endpoint directly. Use this table and table-operation pair to read issues:

API Destination - Jira

Read and write Jira data effortlessly. Track, manage, and automate issues, projects, worklogs, and comments — almost no coding required.

Jira

Issues

Select

There are no parameters to configure.

SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

List issues

Lists all issues

SELECT * FROM Issues

--//Query single issue by numeric Issue Id
--SELECT * FROM Issues Where Id=101234

--//Query issue by numeric Issue Ids (multiple)
--SELECT * FROM Issues WITH(SearchBy='Key', Key='101234,101235,101236')

--//Query issue by Issue Key(s) (alpha-numeric)
--SELECT * FROM Issues WITH(SearchBy='Key', Key='PROJ-11')
--SELECT * FROM Issues WITH(SearchBy='Key', Key='PROJ-11,PROJ-12,PROJ-13')

--//Query issue by project(s)
--SELECT * FROM Issues WITH(SearchBy='Project', Project='PROJ')
--SELECT * FROM Issues WITH(SearchBy='Project', Project='PROJ,KAN,CS')

--//Query issue by JQL expression
--SELECT * FROM Issues WITH(SearchBy='Jql', Jql='status IN (Done, Closed) AND created > -5d' )

List a single issue by Id

List a single issue by Key (e.g. CS-123) or Numeric Id (e.g. 10001).

SELECT * FROM Issues WITH(SearchBy='Key', Key='10001')

List a single issue by Id - Continue on a specific error message

By default if issue is not found or search condition is bad you may get an error but you can continue by setting ContineOnErrorForMessage=1 and message you like to ignore in ErrorSubstringToMatch.

SELECT * FROM Issues WITH(SearchBy='Key', Key='10001', ContineOnErrorForMessage=1, ErrorSubstringToMatch='Issue does not exist')

List a single issue by Key

List a single issue by Key (e.g. CS-123) or Numeric Id (e.g. 10001).

SELECT * FROM Issues WITH(SearchBy='Key', Key='CS-1')

List multiple issues by Id or Key

List multiple issues by comma separated Key(s) or Numeric Id(s).

SELECT * FROM Issues WITH(SearchBy='Key', Key='CS-1,CS-2,10003,10004')

List all issues for a specific project

List all issues for a specified project code. To query multiple project you can use comma separated list. Example: SELECT * FROM Issues WITH(SearchBy='Project', Project='PROJ1,PROJ2,PROJ3')

SELECT * FROM Issues WITH(SearchBy='Project', Project='CS')

List issues (fetch specific fields only rather than all)

Lists all issues and fetch only specified fields rather than all fields (useful to speed up data fetch if you only need handful fields)

SELECT * FROM Issues WITH(Fields='id,key,summary,status')

Search issues using Advanced JQL query expression

List issues using JQL query expression

SELECT * FROM Issues WITH (Jql='status IN (Done, Closed) AND created > -5d' )
/*
Useful links:
  https://support.atlassian.com/jira-work-management/docs/jql-fields/
  https://www.atlassian.com/software/jira/guides/jql/tutorials#advanced-search
  https://www.atlassian.com/blog/jira/jql-the-most-flexible-way-to-search-jira-14
  
Other Possible JQL expressions:

Ids (IN):       key IN(10001, 10002, 10003);
Keys (IN):       key IN(CS-1, CS-2, CS-3);
Projects (IN):       project IN(PROJ1, PROJ2, PROJ3);
Status (EQUAL):       status='Done';
Date (Expression 1):       created >=-5d;
Date (Expression 2):       created >=startOfMonth() AND created <=now();
Date (Expression 3):       created >=startOfYear() AND created <=startOfDay();
Date (Static):       created >= '2008/12/31';
Date (Static with time):       created >= '2008/12/31 23:59';
Project and Status (AND + IN):       project=CS AND status NOT IN ('Done', 'Open', 'Closed');
Assignee and Created Date:    assignee is NOT EMPTY and created < -1d;
Text (Contains - Fuzzy):       Summary ~ 'some words' OR description ~ 'some words';
Text (Contains - Fuzzy Wildcard):       Summary ~ 'some*' OR description ~ 'some*';
Text (Contains - Exact):       Summary ~ '\"exact words\"' OR description ~ '\"exact words\"';
Text (Does Not Contain - Fuzzy):       Summary !~ 'some words' OR description !~ 'some words';
Empty OR Null:   fixVersion is empty OR fixVersion is null;
Is Not Empty OR Is Not Null:    fixVersion is not empty OR fixVersion is not null;
WAS Operator (previous value):       status WAS "Resolved" BY (jsmith,srogen) BEFORE "2019/02/02";
WAS IN Operator:   status WAS IN ("Resolved", "In Progress");
WAS NOT IN Operator:    status WAS NOT IN ("Resolved", "In Progress");
WAS + BY + DURING (date range):       status WAS "Resolved" BY (jsmith,srogen) DURING("2019/02/02", "2020/02/02");
CHANGED operator:    assignee CHANGED;
CHANGED operator (multiple):       status CHANGED FROM "In Progress" TO "Open"

*/

Query Issues with All Columns (Including SLA / Nested Custom Fields)—No META

Simpler approach: set Metadata Mode to MergeStaticDynamic so the driver scans sample data and exposes all columns (static + dynamic). No META parameter needed. Slower due to extra requests used to guess column types from data.

-- No META needed. Set MetaDetectionOrder to MergeStaticDynamic so nested/SLA columns are discovered automatically.
-- Slower: driver makes extra requests to scan data and merge with static metadata.

SELECT *
FROM Issues
WITH(
	  "JQL"='project IN(SUP)'
	  ,"MetaDetectionOrder"='MergeStaticDynamic'
)

Query Custom Fields with Nested Document Structures (SLA Fields)

This example shows how to query custom fields that contain nested document structures (not arrays), such as SLA fields. Use @OverrideMode:1 to merge static and dynamic metadata, allowing you to query both standard fields and nested custom field properties.

-- NOTE: Replace customfield_10084, customfield_10085, customfield_10086 with your own custom field IDs.
-- You can run [SELECT * FROM Fields] in preview tab to get the field IDs. 
-- OR you can find field IDs in Jira: Project Settings > Issue types > Edit field > field ID in URL or API.

SELECT 
-- static fields

  id
, key
, summary
, statusname

-- dynamic fields

-- SLA: Time to Resolution (replace customfield_10084 with your SLA field ID) 

,[fields.customfield_10084.id] as c10084_id
,[fields.customfield_10084.name] as c10084_name
,[fields.customfield_10084.ongoingCycle.startTime.friendly] as c10084_startTime_friendly
,[fields.customfield_10084.ongoingCycle.startTime.jira] as c10084_startTime_jira
,[fields.customfield_10084.ongoingCycle.breachTime.friendly] as c10084_breachTime_friendly
,[fields.customfield_10084.ongoingCycle.breachTime.jira] as c10084_breachTime_jira
,[fields.customfield_10084.ongoingCycle.elapsedTime.friendly] as c10084_elapsedTime_friendly
,[fields.customfield_10084.ongoingCycle.remainingTime.friendly] as c10084_remainingTime_friendly
,[fields.customfield_10084.ongoingCycle.goalDuration.friendly] as c10084_goalDuration_friendly
,[fields.customfield_10084.ongoingCycle.breached] as c10084_breached
,[fields.customfield_10084.ongoingCycle.paused] as c10084_paused

-- SLA: Time to First Response (replace customfield_10085 with your SLA field ID)

,[fields.customfield_10085.id] as c10085_id
,[fields.customfield_10085.name] as c10085_name
,[fields.customfield_10085.ongoingCycle.startTime.friendly] as c10085_startTime_friendly
,[fields.customfield_10085.ongoingCycle.startTime.jira] as c10085_startTime_jira
,[fields.customfield_10085.ongoingCycle.breachTime.friendly] as c10085_breachTime_friendly
,[fields.customfield_10085.ongoingCycle.breachTime.jira] as c10085_breachTime_jira
,[fields.customfield_10085.ongoingCycle.elapsedTime.friendly] as c10085_elapsedTime_friendly
,[fields.customfield_10085.ongoingCycle.remainingTime.friendly] as c10085_remainingTime_friendly
,[fields.customfield_10085.ongoingCycle.goalDuration.friendly] as c10085_goalDuration_friendly
,[fields.customfield_10085.ongoingCycle.breached] as c10085_breached
,[fields.customfield_10085.ongoingCycle.paused] as c10085_paused

-- SLA: Time to Close After Resolution (replace customfield_10086 with your SLA field ID)

,[fields.customfield_10086.id] as c10086_id
,[fields.customfield_10086.name] as c10086_name

-- Other nested custom fields (replace IDs as needed) 
,[fields.customfield_10024.id] as c10024_id
,[fields.customfield_10024.name] as c10024_name
,[fields.customfield_10075.languageCode] as c10075_languageCode
,[fields.customfield_10075.displayName] as c10075_displayName

FROM Issues
WITH(
	  "JQL"='project IN(SUP)'
	  -- @OverrideMode:1 merges metadata so you can use static + dynamic fields together
	  ,META='@OverrideMode:1
	  ;fields.customfield_10084.id : string(10)
	  ;fields.customfield_10084.name : string(180)
	  ;fields.customfield_10084.ongoingCycle.startTime.friendly : string(130)
	  ;fields.customfield_10084.ongoingCycle.startTime.jira : DateTime
	  ;fields.customfield_10084.ongoingCycle.breachTime.friendly : string(130)
	  ;fields.customfield_10084.ongoingCycle.breachTime.jira : DateTime
	  ;fields.customfield_10084.ongoingCycle.elapsedTime.friendly : string(30)
	  ;fields.customfield_10084.ongoingCycle.remainingTime.friendly : string(70)
	  ;fields.customfield_10084.ongoingCycle.goalDuration.friendly : string(30)
	  ;fields.customfield_10084.ongoingCycle.breached : Boolean
	  ;fields.customfield_10084.ongoingCycle.paused : Boolean
	  ;fields.customfield_10085.id : string(10)
	  ;fields.customfield_10085.name : string(220)
	  ;fields.customfield_10085.ongoingCycle.startTime.friendly : string(130)
	  ;fields.customfield_10085.ongoingCycle.startTime.jira : DateTime
	  ;fields.customfield_10085.ongoingCycle.breachTime.friendly : string(130)
	  ;fields.customfield_10085.ongoingCycle.breachTime.jira : DateTime
	  ;fields.customfield_10085.ongoingCycle.elapsedTime.friendly : string(30)
	  ;fields.customfield_10085.ongoingCycle.remainingTime.friendly : string(70)
	  ;fields.customfield_10085.ongoingCycle.goalDuration.friendly : string(30)
	  ;fields.customfield_10085.ongoingCycle.breached : Boolean
	  ;fields.customfield_10085.ongoingCycle.paused : Boolean
	  ;fields.customfield_10086.id : string(10)
	  ;fields.customfield_10086.name : string(300)
	  ;fields.customfield_10024.id : string(40)
	  ;fields.customfield_10024.name : string(80)
	  ;fields.customfield_10075.languageCode : string(20)
	  ;fields.customfield_10075.displayName : string(70)
	  ;--add more nested custom field properties here as needed--
	  '
)

get_issues endpoint belongs to Issues table(s), and can therefore be used via those table(s).

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

List issues

Lists all issues

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues

--//Query single issue by numeric Issue Id
--SELECT * FROM Issues Where Id=101234

--//Query issue by numeric Issue Ids (multiple)
--SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''101234,101235,101236'')

--//Query issue by Issue Key(s) (alpha-numeric)
--SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''PROJ-11'')
--SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''PROJ-11,PROJ-12,PROJ-13'')

--//Query issue by project(s)
--SELECT * FROM Issues WITH(SearchBy=''Project'', Project=''PROJ'')
--SELECT * FROM Issues WITH(SearchBy=''Project'', Project=''PROJ,KAN,CS'')

--//Query issue by JQL expression
--SELECT * FROM Issues WITH(SearchBy=''Jql'', Jql=''status IN (Done, Closed) AND created > -5d'' )';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List a single issue by Id

List a single issue by Key (e.g. CS-123) or Numeric Id (e.g. 10001).

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''10001'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List a single issue by Id - Continue on a specific error message

By default if issue is not found or search condition is bad you may get an error but you can continue by setting ContineOnErrorForMessage=1 and message you like to ignore in ErrorSubstringToMatch.

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''10001'', ContineOnErrorForMessage=1, ErrorSubstringToMatch=''Issue does not exist'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List a single issue by Key

List a single issue by Key (e.g. CS-123) or Numeric Id (e.g. 10001).

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''CS-1'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List multiple issues by Id or Key

List multiple issues by comma separated Key(s) or Numeric Id(s).

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Key'', Key=''CS-1,CS-2,10003,10004'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List all issues for a specific project

List all issues for a specified project code. To query multiple project you can use comma separated list. Example: SELECT * FROM Issues WITH(SearchBy='Project', Project='PROJ1,PROJ2,PROJ3')

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(SearchBy=''Project'', Project=''CS'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

List issues (fetch specific fields only rather than all)

Lists all issues and fetch only specified fields rather than all fields (useful to speed up data fetch if you only need handful fields)

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH(Fields=''id,key,summary,status'')';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

Search issues using Advanced JQL query expression

List issues using JQL query expression

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM Issues WITH (Jql=''status IN (Done, Closed) AND created > -5d'' )
/*
Useful links:
  https://support.atlassian.com/jira-work-management/docs/jql-fields/
  https://www.atlassian.com/software/jira/guides/jql/tutorials#advanced-search
  https://www.atlassian.com/blog/jira/jql-the-most-flexible-way-to-search-jira-14
  
Other Possible JQL expressions:

Ids (IN):       key IN(10001, 10002, 10003);
Keys (IN):       key IN(CS-1, CS-2, CS-3);
Projects (IN):       project IN(PROJ1, PROJ2, PROJ3);
Status (EQUAL):       status=''Done'';
Date (Expression 1):       created >=-5d;
Date (Expression 2):       created >=startOfMonth() AND created <=now();
Date (Expression 3):       created >=startOfYear() AND created <=startOfDay();
Date (Static):       created >= ''2008/12/31'';
Date (Static with time):       created >= ''2008/12/31 23:59'';
Project and Status (AND + IN):       project=CS AND status NOT IN (''Done'', ''Open'', ''Closed'');
Assignee and Created Date:    assignee is NOT EMPTY and created < -1d;
Text (Contains - Fuzzy):       Summary ~ ''some words'' OR description ~ ''some words'';
Text (Contains - Fuzzy Wildcard):       Summary ~ ''some*'' OR description ~ ''some*'';
Text (Contains - Exact):       Summary ~ ''\"exact words\"'' OR description ~ ''\"exact words\"'';
Text (Does Not Contain - Fuzzy):       Summary !~ ''some words'' OR description !~ ''some words'';
Empty OR Null:   fixVersion is empty OR fixVersion is null;
Is Not Empty OR Is Not Null:    fixVersion is not empty OR fixVersion is not null;
WAS Operator (previous value):       status WAS "Resolved" BY (jsmith,srogen) BEFORE "2019/02/02";
WAS IN Operator:   status WAS IN ("Resolved", "In Progress");
WAS NOT IN Operator:    status WAS NOT IN ("Resolved", "In Progress");
WAS + BY + DURING (date range):       status WAS "Resolved" BY (jsmith,srogen) DURING("2019/02/02", "2020/02/02");
CHANGED operator:    assignee CHANGED;
CHANGED operator (multiple):       status CHANGED FROM "In Progress" TO "Open"

*/';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

Query Issues with All Columns (Including SLA / Nested Custom Fields)—No META

DECLARE @MyQuery NVARCHAR(MAX) = '-- No META needed. Set MetaDetectionOrder to MergeStaticDynamic so nested/SLA columns are discovered automatically.
-- Slower: driver makes extra requests to scan data and merge with static metadata.

SELECT *
FROM Issues
WITH(
	  "JQL"=''project IN(SUP)''
	  ,"MetaDetectionOrder"=''MergeStaticDynamic''
)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

Query Custom Fields with Nested Document Structures (SLA Fields)

DECLARE @MyQuery NVARCHAR(MAX) = '-- NOTE: Replace customfield_10084, customfield_10085, customfield_10086 with your own custom field IDs.
-- You can run [SELECT * FROM Fields] in preview tab to get the field IDs. 
-- OR you can find field IDs in Jira: Project Settings > Issue types > Edit field > field ID in URL or API.

SELECT 
-- static fields

  id
, key
, summary
, statusname

-- dynamic fields

-- SLA: Time to Resolution (replace customfield_10084 with your SLA field ID) 

,[fields.customfield_10084.id] as c10084_id
,[fields.customfield_10084.name] as c10084_name
,[fields.customfield_10084.ongoingCycle.startTime.friendly] as c10084_startTime_friendly
,[fields.customfield_10084.ongoingCycle.startTime.jira] as c10084_startTime_jira
,[fields.customfield_10084.ongoingCycle.breachTime.friendly] as c10084_breachTime_friendly
,[fields.customfield_10084.ongoingCycle.breachTime.jira] as c10084_breachTime_jira
,[fields.customfield_10084.ongoingCycle.elapsedTime.friendly] as c10084_elapsedTime_friendly
,[fields.customfield_10084.ongoingCycle.remainingTime.friendly] as c10084_remainingTime_friendly
,[fields.customfield_10084.ongoingCycle.goalDuration.friendly] as c10084_goalDuration_friendly
,[fields.customfield_10084.ongoingCycle.breached] as c10084_breached
,[fields.customfield_10084.ongoingCycle.paused] as c10084_paused

-- SLA: Time to First Response (replace customfield_10085 with your SLA field ID)

,[fields.customfield_10085.id] as c10085_id
,[fields.customfield_10085.name] as c10085_name
,[fields.customfield_10085.ongoingCycle.startTime.friendly] as c10085_startTime_friendly
,[fields.customfield_10085.ongoingCycle.startTime.jira] as c10085_startTime_jira
,[fields.customfield_10085.ongoingCycle.breachTime.friendly] as c10085_breachTime_friendly
,[fields.customfield_10085.ongoingCycle.breachTime.jira] as c10085_breachTime_jira
,[fields.customfield_10085.ongoingCycle.elapsedTime.friendly] as c10085_elapsedTime_friendly
,[fields.customfield_10085.ongoingCycle.remainingTime.friendly] as c10085_remainingTime_friendly
,[fields.customfield_10085.ongoingCycle.goalDuration.friendly] as c10085_goalDuration_friendly
,[fields.customfield_10085.ongoingCycle.breached] as c10085_breached
,[fields.customfield_10085.ongoingCycle.paused] as c10085_paused

-- SLA: Time to Close After Resolution (replace customfield_10086 with your SLA field ID)

,[fields.customfield_10086.id] as c10086_id
,[fields.customfield_10086.name] as c10086_name

-- Other nested custom fields (replace IDs as needed) 
,[fields.customfield_10024.id] as c10024_id
,[fields.customfield_10024.name] as c10024_name
,[fields.customfield_10075.languageCode] as c10075_languageCode
,[fields.customfield_10075.displayName] as c10075_displayName

FROM Issues
WITH(
	  "JQL"=''project IN(SUP)''
	  -- @OverrideMode:1 merges metadata so you can use static + dynamic fields together
	  ,META=''@OverrideMode:1
	  ;fields.customfield_10084.id : string(10)
	  ;fields.customfield_10084.name : string(180)
	  ;fields.customfield_10084.ongoingCycle.startTime.friendly : string(130)
	  ;fields.customfield_10084.ongoingCycle.startTime.jira : DateTime
	  ;fields.customfield_10084.ongoingCycle.breachTime.friendly : string(130)
	  ;fields.customfield_10084.ongoingCycle.breachTime.jira : DateTime
	  ;fields.customfield_10084.ongoingCycle.elapsedTime.friendly : string(30)
	  ;fields.customfield_10084.ongoingCycle.remainingTime.friendly : string(70)
	  ;fields.customfield_10084.ongoingCycle.goalDuration.friendly : string(30)
	  ;fields.customfield_10084.ongoingCycle.breached : Boolean
	  ;fields.customfield_10084.ongoingCycle.paused : Boolean
	  ;fields.customfield_10085.id : string(10)
	  ;fields.customfield_10085.name : string(220)
	  ;fields.customfield_10085.ongoingCycle.startTime.friendly : string(130)
	  ;fields.customfield_10085.ongoingCycle.startTime.jira : DateTime
	  ;fields.customfield_10085.ongoingCycle.breachTime.friendly : string(130)
	  ;fields.customfield_10085.ongoingCycle.breachTime.jira : DateTime
	  ;fields.customfield_10085.ongoingCycle.elapsedTime.friendly : string(30)
	  ;fields.customfield_10085.ongoingCycle.remainingTime.friendly : string(70)
	  ;fields.customfield_10085.ongoingCycle.goalDuration.friendly : string(30)
	  ;fields.customfield_10085.ongoingCycle.breached : Boolean
	  ;fields.customfield_10085.ongoingCycle.paused : Boolean
	  ;fields.customfield_10086.id : string(10)
	  ;fields.customfield_10086.name : string(300)
	  ;fields.customfield_10024.id : string(40)
	  ;fields.customfield_10024.name : string(80)
	  ;fields.customfield_10075.languageCode : string(20)
	  ;fields.customfield_10075.displayName : string(70)
	  ;--add more nested custom field properties here as needed--
	  ''
)';

EXEC (@MyQuery) AT [LS_TO_JIRA_IN_GATEWAY];

get_issues endpoint belongs to Issues table(s), and can therefore be used via those table(s).