SQL query examples
The ZappySys API Driver is a user-friendly interface designed to facilitate the seamless integration of various applications with the Jira API. With its intuitive design and robust functionality, the ZappySys API Driver simplifies the process of configuring specific API endpoints to efficiently read or write data from Jira.
On this page you will find some SQL examples which can be used for API ODBC Driver or Data Gateway API Connector.
List issues
Lists all issues [ Read more... ]
SELECT * FROM Issues
List a single issue by Id
List a single issue by Key (e.g. CS-123) or Numeric Id (e.g. 10001). [ Read more... ]
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. [ Read more... ]
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). [ Read more... ]
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). [ Read more... ]
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. [ Read more... ]
SELECT * FROM Issues WITH(Project='CS')
Search issues using Advanced JQL query expression
List issues using JQL query expression [ Read more... ]
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"
*/
INSERT Issue
Inserts a single issue to a particular project [ Read more... ]
INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description)
VALUES('SMP', 'Task', 'My ticket inserted through the API', 'A description about an issue')
WITH (Output=1)
INSERT Multiple Issues (BULK Load from External System like Microsoft SQL Server / ODBC)
Inserts multiple issues from an external source (e.g. Microsoft SQL Server or ODBC) [ Read more... ]
INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description)
SOURCE('MSSQL' --OR ODBC
, 'Data Source=localhost;Initial Catalog=Northwind;Integrated Security=true'
--For ODBC
--, 'Dsn=MyOdbcDsn'
--, 'Driver={My ODBC Driver Type}...'
--Alias must match Supported Input Columns - Use Query Builder to find Input Column Names
,'select top 10 C_ProjKey as ProjectKey, C_Type as IssueTypeName, C_Summary as Summary, C_Desc as Description FROM Orders'
--Static value example
--,'select top 10 ''CS'' as ProjectKey, ''New Feature'' as IssueTypeName, ''Task created - <<FUN_SEQUENCE>>'' Summary, ''A description <<FUN_NOW>>'' Description FROM Orders'
)
UPDATE Issue
Updates an issue [ Read more... ]
UPDATE Issues
SET Summary = 'This is my summary'
,Description = 'Lot''s of stuff to describe'
,Labels = '[ "bugfix" ]'
,DueDate = '2029-10-10'
--WHERE Id=1234
--WHERE Id='ISSKEY'
WITH (
IssueIdOrKey='ISSKEY', --or use Id in where clause
Output=1,
NotifyUsers=0,
OverrideScreenSecurity=0,
OverrideEditableFlag=0,
ContinueOn404Error=0
)'
UPDATE / Insert Issue with Custom Fields
Updates or Insert an issue with custom fields of various types (dropdown, radio, textarea .. so on) [ Read more... ]
UPDATE Issues
SET customfield_10050='[{"value":"AAA"},{"value":"BBB"}]' --CUSTOM Checkboxes field update (Must use Raw JSON)
,customfield_10051='2020-12-31' --CUSTOM Date field update
,customfield_10052='2020-12-31T23:59:59' --CUSTOM DateTime field update
--Custom Dropdown / Radio fields
,customfield_10048_value='BBB' --CUSTOM Dropdown field update (Using value - i.e. item label)
--OR--
--,customfield_10048_id='10022' --CUSTOM Dropdown field update (Using id - i.e. item id)
--OR--
--,customfield_10048='{"value":"BBB"}' --CUSTOM Dropdown (Using Raw value)
,customfield_10053='["bugfix","test"]' --CUSTOM Labels field update (Must use Raw JSON)
,customfield_10057='[{"value":"AAA"},{"value":"BBB"}]' --CUSTOM Listbox Multiselect field update (Must use Raw JSON)
,customfield_10049=123455555.123 --CUSTOM Number field update
,customfield_10054_text='Long string...line-1
Long string... line-2
Long string... line-3' --CUSTOM Paragraph field update
,customfield_10055_value='BBB' --CUSTOM Radio field update by value
--OR--
--,customfield_10055_id='10023' --CUSTOM Radio field update
,customfield_10058='https://zappysys.com' --CUSTOM Url field update
,customfield_10059_accountId="5dd64082af96bc0efbe55103" --CUSTOM User field update (update by accountId)
--OR--
--,customfield_10059='{"accountId":"5dd64082af96bc0efbe55103","displayName":"System"}' --CUSTOM User field update --accountId=5dd64082af96bc0efbe55103
,customfield_10060='Custom text single line' --CUSTOM text single line field
,customfield_10001='577069e1-1bcd-4b1e-9070-0b2475830d1c' --CUSTOM Team field update (update by Team Id)
--WHERE Id=10000
WITH (
IssueIdOrKey='ISSKEY', --or use Id in where clause
Output=1,
NotifyUsers=0,
OverrideScreenSecurity=0,
OverrideEditableFlag=0,
ContinueOn404Error=0
)
--For INSERT use same values with same fields like below. For Insert you do not have to specify Id or Key
--INSERT INTO Issues(ProjectKey, IssueTypeName, Summary, Description, customfield_10050,....., customfield_10060)
--VALUES ('SMP', 'Task', 'My ticket inserted through the API', 'A description about an issue', '[{"value":"AAA"},{"value":"BBB"}]' ,... 'Custom text single line')
'
UPDATE multiple Issues matching with JQL search query
Updates multiple issues which match with certain condition (JQL). Adding Where 1=1 or some other WHERE condition invokes Lookup endpoint (refer to other example to learn about JQL - see Search issues using Advanced JQL query expression) [ Read more... ]
UPDATE Issues
SET Summary = 'This is done on <<fun_now>>'
Where 1=1
WITH (jql='status=done' , ContinueOn404Error=0)
--WITH (jql='key in(10001, 10002, 10003)' , ContinueOn404Error=0)
--WITH (jql='key in(CS-1, CS-2, CS-3)', ContinueOn404Error=0)
DELETE Issue
Deletes a single issue [ Read more... ]
DELETE FROM Issues
WITH (IssueIdOrKey='10020', OUTPUT=1, ContinueOn404Error=0)
DELETE multiple Issues matching with JQL search query
Delete multiple issues which match with certain condition (JQL). Adding Where 1=1 or some other WHERE condition invokes Lookup endpoint (refer to other example to learn about JQL - see Search issues using Advanced JQL query expression) [ Read more... ]
DELETE FROM Issues
Where 1=1
WITH (jql='status=done' , ContinueOn404Error=0)
--WITH (jql='key in(10001, 10002, 10003)' , ContinueOn404Error=0)
--WITH (jql='key in(CS-1, CS-2, CS-3)', ContinueOn404Error=0)
List comments for all issues
List comments for all issues [ Read more... ]
SELECT * FROM Comments
List comments for a specific issue Issue Key or Id
List comments for a specific issue [ Read more... ]
SELECT * FROM Comments Where IssueId=10003 --OR WITH(Key='10003')
List comments for a specific issue by Key
List comments for a specific issue by key [ Read more... ]
SELECT * FROM Comments WITH(Key='CS-1')
List comments for issues (search by JQL)
List all comments for issues returned from a JQL search expression (refer to previous example to learn about JQL - see Search issues using Advanced JQL query expression) [ Read more... ]
SELECT * FROM Comments WITH (Jql='status IN (Done, Closed) AND created > -5d' )
Create a new Issue Comment (Plain Text Body)
This example creates a new plain text comment for a given Issue Id. You can use Issue Key or Id as an input value. [ Read more... ]
INSERT INTO Comments(IssueId,Body) VALUES('CS-2', 'Commented at <<FUN_NOW>>')
Create a new Issue Comment (Formatted Body)
This example creates a new formatted text comment for a given Issue Id. You can use Issue Key or Id as an input value. [ Read more... ]
INSERT INTO Comments(IssueId,BodyFormatted) VALUES('CS-3', '{ "content": [
{ "content": [
{ "text": "This is a valid ADF formatted comment.",
"type": "text" }
],
"type": "paragraph"
}
],
"type": "doc",
"version": 1
}
}')
Update an exising Comment (Plain Text Body)
This example updates an exising comment for a given Issue Id and Comment Id. You can use Issue Key or Id as an input value. [ Read more... ]
UPDATE Comments
SET Body='Updated at <<FUN_NOW>>'
WHERE Id=10004
WITH(IssueIdOrKey='CS-2')
Delete an exising Comment
This example deletes an exising comment for a given Issue Id and Comment Id. You can use Issue Key or Id as an input value. [ Read more... ]
DELETE FROM Comments WHERE Id=10004 WITH(IssueIdOrKey='CS-2')
List fields
Lists all fields that are used and available in issue entity [ Read more... ]
SELECT * FROM Fields
List projects
Lists all available projects [ Read more... ]
SELECT * FROM Projects
List worklog entries for a specific issue by Id or Key
List all worklog entries for a specific issue [ Read more... ]
SELECT * FROM Worklogs Where IssueId=10003 --WITH(Key='10003')
List worklogs for issues (search by JQL)
List all worklogs (time entries) for issues returned from a JQL search expression (refer to previous example to learn about JQL - see Search issues using Advanced JQL query expression) [ Read more... ]
SELECT * FROM Worklogs WITH (Jql='status IN (Done, Closed) AND created > -5d' )
INSERT Project
Inserts a single project [ Read more... ]
INSERT INTO Projects(ProjectKey, Name, ProjectTypeKey, LeadAccountId, AssigneeType)
VALUES ('TEST', 'Test Project', 'software', '70122:XXXXXXXX-XXXX-XXXX-XXXX-c5da8c98b9e2', 'PROJECT_LEAD)
WITH (Output=1)')
UPDATE Project
Updates a single project [ Read more... ]
UPDATE Projects
SET Name = 'My Test Kanban Project'
,ProjectCategoryId = 1
WITH (ProjectIdOrKey = 'MYPRJCT', Output=1, ContinueOn404Error=0)
DELETE Project
Deletes a single project [ Read more... ]
DELETE FROM Projects
WITH (ProjectIdOrKey = '10020', Output=1, ContinueOn404Error=0)
List users
Lists all available users [ Read more... ]
SELECT * FROM Users
INSERT User
Inserts a single user [ Read more... ]
INSERT INTO Users(EmailAddress, DisplayName, Name, Password)
VALUES ('my@user.com', 'John Doe', 'John', 'xhedkspstdadaothoua')
WITH (OUTPUT=1)
DELETE User
Deletes a single user [ Read more... ]
DELETE FROM Users
WITH (OUTPUT=1, accountId = '547059:136095a0-XXXX-XXXX-XXXX-3e4c66f26551', ContinueOn404Error=0)
List worklogs
Lists all worklogs from all issues [ Read more... ]
SELECT * FROM Worklogs
INSERT Worklog
Inserts a single worklog to a particular issue [ Read more... ]
INSERT INTO Worklogs(TimeSpentInSeconds, Comment, StartedAt)
VALUES(7200,'My Comment!','2020-02-23T16:20:30.123+0000')
WITH (IssueIdOrKey='ISSKEY-1', OUTPUT=1)
UPDATE Worklog
Updates a worklog [ Read more... ]
UPDATE Worklogs
SET TimeSpentInSeconds = 28800
,Comment='My Comment!'
,StartedAt='2020-01-23T16:20:30.123+0000'
WITH (IssueIdOrKey='MTK-1', WorklogId='123465', OUTPUT=1, ContinueOn404Error=0)
DELETE Worklog
Deletes a single worklog of an issue [ Read more... ]
DELETE FROM Worklogs
WITH (IssueIdOrKey='10020', WorklogId='123465', OUTPUT=1, ContinueOn404Error=0)
Update Custom Option Field (Dropdown/Radio)
This example shows how to update a custom field of an issue by Option Value or Id (Of option entry item) [ Read more... ]
--(By value)
UPDATE Issues
SET customfield_10048_value ='BBB' --supply value (label) of dropdown/radio
WITH (IssueIdOrKey='10020')
--OR-- (By item ID)
UPDATE Issues
SET customfield_10048_id =10023 --supply id of dropdown/radio item
WITH (IssueIdOrKey='10020')
--OR-- (Raw id)
UPDATE Issues
SET customfield_10048='{"id":"10023"}' --supply raw json
WITH (IssueIdOrKey='10020')
--OR-- (Raw value)
UPDATE Issues
SET customfield_10048='{"value":"BBB"}' --supply raw json
WITH (IssueIdOrKey='10020')
--OR-- (set null)
UPDATE Issues
SET customfield_10048 =null
WITH (IssueIdOrKey='10020')
Update Custom Multi Select / User / Team fields (RAW Json Update)
This example shows how to update a custom field of type array or other complex fiels like user [ Read more... ]
UPDATE Issues
SET customfield_10048='[{"value":"AAA"}, {"value":"CCC"}]' --supply raw json
WITH (IssueIdOrKey='10020')
--OR--
UPDATE Issues
SET customfield_10048 =null --set to null
WITH (IssueIdOrKey='10020')
Getting Started with Examples
ZappySys API Driver is a powerful software solution designed to facilitate the extraction and integration of data from a wide range of sources through APIs. Its intuitive design and extensive feature set make it an essential asset for any organization dealing with complex data integration tasks.
To get started with examples using ZappySys API Driver, please click on the following applications:
Key features of the ZappySys API Driver include:
The API ODBC driver facilitates the reading and writing of data from numerous popular online services (refer to the complete list here) using familiar SQL language without learning complexity of REST API calls. The driver allows querying nested structure and output as a flat table. You can also create your own ODBC / Data Gateway API connector file and use it with this driver.
Intuitive Configuration: The interface is designed to be user-friendly, enabling users to easily set up the specific API endpoints within Jira without requiring extensive technical expertise or programming knowledge.
Customizable Endpoint Setup: Users can conveniently configure the API endpoint settings, including the HTTP request method, endpoint URL, and any necessary parameters, to precisely target the desired data within Jira.
Data Manipulation Capabilities: The ZappySys API Driver allows for seamless data retrieval and writing, enabling users to fetch data from Jira and perform various data manipulation operations as needed, all through an intuitive and straightforward interface.
Secure Authentication Integration: The driver provides secure authentication integration, allowing users to securely connect to the Jira API by inputting the necessary authentication credentials, such as API tokens or other authentication keys.
Error Handling Support: The interface is equipped with comprehensive error handling support, ensuring that any errors or exceptions encountered during the data retrieval or writing process are efficiently managed and appropriately communicated to users for prompt resolution.
Data Visualization and Reporting: The ZappySys API Driver facilitates the seamless processing and presentation of the retrieved data from Jira, enabling users to generate comprehensive reports and visualizations for further analysis and decision-making purposes.
Overall, the ZappySys API Driver serves as a powerful tool for streamlining the integration of applications with Jira, providing users with a convenient and efficient way to access and manage data, all through a user-friendly and intuitive interface.