/
Software (Jira Java API)

Software (Jira Java API)

Nov 18, 2020



Introduction

SQL for JIRA wraps the JIRA Software Java API for reporting (only GET methods are supported → Read-only). Therefore, it is intended for Request-based reports.

All the database tables are under the SOFTWARE schema. You have to join the PUBLIC schema tables to get JIRA Core/Business data related to issues, projects, versions, etc.



Consideration

The use of the JIRA Software tables is identical to the use of the Kambas or Scrum boards. This means that the results returned by the queries will be the same as if the boards are used directly.



For example, when planning a sprint, only tasks can be planned:





The subtasks take the sprint from the parent issue automatically; and the epics do not belong to any Sprint (because the epics can not be planned).
Even if you fill the custom field in Epic:







If we make a query to get the issues of sprint 3, we will get the following issues:





The results do not contain the epic, because sprints can only contain tasks (and their subtasks).



JIRA Software Documentation

You can consult the documentation of JIRA Software in the following link JIRA Software Server 7.4 documentation for more information.



Entity Relationship Diagram (ERD)

The model entity-relationship for Jira Software is as follows:



Epic Issues

BOARDS

BOARDS

COLUMN

TYPE

INDEXES



EPICID

BIGINT

X

The id of the Epic.

ISSUEID

BIGINT



The id of the Issue belonging to the Epic



The EPICISSUES table (available from the 9.8.0 version) is a convenient way of getting all the Issues belonging to a given Epic that performs better than the BOARDEPICISSUES table.

The EPICID  must correspond to an Epic issue, otherwise, the table raises an error.



Boards

A board displays issues from one or more projects, giving you a flexible way of viewing, managing, and reporting on work in progress. There are two types of boards in JIRA Software:

  • Scrum board — for teams that plan their work in sprints

  • Kanban board — for teams that focus on managing and constraining their work-in-progress

(View What is a board?)

Columns

BOARDS

BOARDS

COLUMN

TYPE

INDEXES



ID

BIGINT

X

The id of the board.

NAME

VARCHAR



The name of the board.

TYPE

VARCHAR



The type of the board. It could be either SCRUM or KANBAN.

SAVEDFILTERID

BIGINT



The id of the filter that determines which issues belong to board.

ISSPRINTSUPPORTENABLED

BOOLEAN



'true' if the board supports sprints.

SHOWDAYSINCOLUMN

BOOLEAN



'true' if the board shows days in column.

CARDCOLORSTRATEGYNAME

VARCHAR



The card color strategy name of the board.

SWIMLANESTRATEGYNAME

VARCHAR



The swimlane strategy name of the board.



BOARDFILTERS

BOARDFILTERS

COLUMN

TYPE

INDEXES



ID

BIGINT

X

The id of the entity.

NAME

VARCHAR



The name of the entity. Must not be null or empty.

DESCRIPTION

VARCHAR



General description of the entity. May be null or empty.

OWNER

VARCHAR



Filter owner

QUERY

VARCHAR



Gets the query that defines the search that will be performed for this filter.

ISLOADED

BOOLEAN



'true' if the filter has been loaded.

ISMODIFIED

BOOLEAN



'true' if the filter has been modified.

FAVOURITECOUNT

BIGINT



The number of users who have marked this entity as one of their favourites.



Examples

Get boards
SELECT * FROM SOFTWARE.BOARDS





Get the filter on a board
SELECT b.id as "Board Id.", b.name, bf.* FROM SOFTWARE.BOARDS b INNER JOIN SOFTWARE.BOARDFILTERS bf on b.SAVEDFILTERID = bf.ID WHERE b.id=2



Board Projects

A board displays issues from one or more projects. You can know what projects are the issues your board shows.
(View Starting a new project, Leading an agile project)





Columns

BOARDPROJECTS

BOARDPROJECTS

COLUMN

TYPE

INDEXES



PROJECTID

BIGINT

X

Project identifier

BOARDID

BIGINT

X

Board identifier



Examples

Get the boards for a project
SELECT p.id as "Project Id.", p.key, p.name, b.id as "Board Id.", b.name, b.type FROM SOFTWARE.BOARDPROJECTS bp INNER JOIN PROJECTS p on p.id=bp.PROJECTID INNER JOIN SOFTWARE.BOARDS b on b.id=bp.boardid WHERE projectid = 10100





Get the projects for a board
SELECT p.id as "Project Id.", p.key, p.name, b.id as "Board Id.", b.name, b.type FROM SOFTWARE.BOARDPROJECTS bp INNER JOIN PROJECTS p on p.id=bp.PROJECTID INNER JOIN SOFTWARE.BOARDS b on b.id=bp.boardid WHERE bp.boardid=1





Board Versions

Versions are points-in-time for a project. They help you schedule and organize your releases.
Once a version is created, and issues are assigned to it, a Releases link will be available in your project navigation sidebar. 
Assigning issues to versions helps you plan the order in which new features (stories) for your application will be released to your customers.
(View Configuring versions in a Kanban project, Configuring versions in a Scrum project)



Columns

BOARDVERSIONS

BOARDVERSIONS

COLUMN

TYPE

INDEXES



BOARDID

BIGINT

X

Board identifier.

VERSIONID

BIGINT



Version identifier



Examples

Get versions for a board
SELECT bv.boardid, pv.* FROM SOFTWARE.BOARDVERSIONS bv INNER JOIN PROJECTVERSIONS pv on bv.versionid=pv.id where boardid=2





Get issues for version of a board
SELECT bv.boardid, pv.id as "Version Id.", pv.name as "Version Name", i.id as "Issue Id.", i.key as "Issue Key" FROM SOFTWARE.BOARDVERSIONS bv INNER JOIN PROJECTVERSIONS pv on pv.id = bv.versionid INNER JOIN ISSUEVERSIONS iv on iv.versionid = bv.versionid INNER JOIN issues i on i.id=iv.issueid where boardid=2 and bv.versionid=10101





Board Columns

The vertical columns in both the Active sprints of a Scrum board and the Kanban board represent the workflow of your board's project.
The default columns in the Active Sprints of a Scrum board are To Do, In Progress, and Done.
The default columns on a Kanban board are Backlog, Selected for Development, In Progress, and Done.
(View Configuring columns, Configuring a board)



Columns

BOARDCOLUMNS

BOARDCOLUMNS

COLUMN

TYPE

INDEXES



BOARDID

BIGINT

X

Board identifier.

ID

BIGINT



Column Identifier.

NAME

VARCHAR



Column name.

POSITION

BIGINT



Column position.

MINIMUM

BIGINT



Minimum number of issues in column.

MAXIMUM

BIGINT



Maximum number of issues in column.

ISVALID

BOOLEAN



'true' if column is valid.

ISVISIBLE

BOOLEAN



'true' if column is visible.



BOARDCOLUMNSTATUSES

BOARDCOLUMNSTATUSES

COLUMN

TYPE

INDEXES



BOARDID

BIGINT

X

Board identifier.

STATUSID

BIGINT



Status identifier.

COLUMNID

BIGINT



Column Identifier.



BOARDCOLUMNPROGRESS

BOARDCOLUMNPROGRESS

COLUMN

TYPE

INDEXES



BOARDID

BIGINT

X

Board identifier.

STATUSID

BIGINT



Status identifier.

PROGRESS

VARCHAR



The breakdown of status mapped in the view to their respective Column Progress.



Examples

Get columns for a board
SELECT * FROM SOFTWARE.BOARDCOLUMNS WHERE boardid=2





Get the mapped states of a workflow in the columns of a board.
SELECT bcs.BOARDID, bcs.STATUSID, isd.NAME as "Status Name", bcs.COLUMNID, bc.NAME as "Column Name" FROM SOFTWARE.BOARDCOLUMNSTATUSES bcs INNER JOIN SOFTWARE.BOARDCOLUMNS bc on bc.ID = bcs.COLUMNID AND bc.BOARDID = bcs.BOARDID INNER JOIN ISSUESTATUSDEFINITIONS isd on isd.ID = bcs.STATUSID WHERE bcs.boardid=2





Obtain the mapping of the progress process for the states included in a board
SELECT bcp.BOARDID, bcp.STATUSID, isd.name as "Status Name", bcp.PROGRESS FROM SOFTWARE.BOARDCOLUMNPROGRESS bcp INNER JOIN ISSUESTATUSDEFINITIONS isd on isd.ID = bcp.STATUSID WHERE boardid=2

Explanation

In the process of resolving an issue:

  1. The "Backlog" status indicates that the problem is not being resolved.

  2. The "Selected for Development" and "In Progress" states that the issue is being resolved.

  3. The "Done" status indicates that the issue has been resolved.





Board Issues

You can get the issues of a certain board through the board identifier, regardless of the sprint, epic ... in which they are included.
There are particularities depending on the type of board:

  • Scrum board — the epics are not part of the set of issues.

  • Kanban board — the epics are part of the set of issues.



Columns

BOARDISSUES

BOARDISSUES

COLUMN

TYPE

INDEXES



BOARDID

BIGINT

X

Board identifier.

EPICID

BIGINT