Data mapping describes how elements are mapped between two distinct data models. To support SQL access to a Jira service, Jira REST endpoints must be mapped to a relational schema. The driver automatically generates a relational view of your data the first time you execute a SQL statement. When generating the relational view, the driver decompounds JSON documents stored at endpoints into parent-child tables. The driver handles mapping the following manner:
  • Simple and nested objects are flattened and mapped to a parent table
  • Arrays are mapped to related child tables

For example, the following JSON document contains nested objects in the issuetype object and an array in the labels object. 

{
    "id": "10000",
    "key": "ABC-100",
    "fields": {
        "issuetype": {
            "id": "20003",
            "description": "A problem that impairs the functions of the product.",
            "name": "Bug",
        },
    "labels": [
         "Dev",
         "Severe",
         "v7.1"
       ],
}
{
    "id": "10001",
    "key": "ABC-101",
    "fields": {
        "issuetype": {
            "id": "20022",
            "description": "An user story.",
            "name": "Story",
        },
    "labels": [
         "Dev",
         "Installer"
         "v8.0"
      ],
}

When generating the relational view, the driver decompounds native arrays into separate, but related tables. The mapping of the sample JSON document produced one parent table and two child tables. In the parent table, simple objects, such as id and key, are flattened into corresponding relational columns. Nested objects are also flattened into relational columns; however, column names are formed by concatenating the name of the parent and nested objects, which are joined by an underscore character. For example, the FIELDS_ISSUETYPE_NAME column contains the values of the name object that is nested in the fields and issuetype objects.

The names of parent tables are determined by the Jira REST API resource used to issue requests. For a list of common tables, refer to "Introduction to the Jira Data Model."

The parent table for our example uses the Issues API resource, and therefore, is named ISSUES. The table takes the following form:

Table 1. ISSUES
ID (PK) KEY FIELDS_ISSUETYPE_ID FIELDS_ISSUETYPE_DESCRIPTION FIELDS_ISSUETYPE_NAME
10000 ABC-100 20003 A problem that impairs the functions of the product Bug
10001 ABC-101 20022 An user story. Story

The information in the labels array normalizes to the ISSUELABELS child table. The values of the array are mapped into a single relational column that corresponds to the name of the array. For example, the values for the labels array in the JSON sample, such as Dev and Severe, map to the ISSUELABEL column in the ISSUELABELS table. A foreign key relationship to the parent table is provided by including the primary key of the parent in the child, in this case, ISSUES_ID. The primary key of the child table is a composite key formed by the primary key of the parent table combined with the positional information contained in the POSITION column. If the array is nested multiple layers deep, additional positional columns for parent objects are mapped to insure that a unique key is used.

The child table for the ISSUELABLES array would take the following form:

Table 2. ISSUELABELS
ISSUES_ID (PK) POSITION (PK) ISSUELABEL
10000 0 Dev
10000 1 Severe
10000 2 v7.1
10001 0 Dev
10001 1 Installer
10001 2 v7.1