Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good day all
I managed to extract JIRA data using the Qlik Rest Connector in Qlik Sense.
I now need to limit the amount of data being pulled with a "where". Ideally i only want to pull data from 2017 onward, based on the "created" field (date field).
Below is the script i am using. It looks like SQL, or JSON SQL, not sure?
Could someone please help me with the line of code i (where clause) need to use to limit the data?
RestConnectorMasterTable:
SQL SELECT
"total" AS "total_u3",
"__KEY_root",
(SELECT
"expand",
"id" AS "id_u29",
// "self" AS "self_u37",
"key" AS "key_u13",
"__KEY_issues",
"__FK_issues",
(SELECT
"customfield_13100",
"resolution",
"customfield_10510",
"customfield_10500",
"customfield_12008",
"customfield_12007",
"customfield_10506",
"customfield_12806",
"customfield_10507",
"customfield_12807",
"lastViewed",
"customfield_14300",
"customfield_12000",
"customfield_12002",
"customfield_12001",
"customfield_12004",
"customfield_12003",
"customfield_12006",
"customfield_12005",
"customfield_11700",
"aggregatetimeoriginalestimate",
"customfield_12107",
"customfield_12106",
"customfield_10601",
"customfield_12901",
"customfield_12108",
"customfield_14400",
"customfield_12101",
"customfield_12100",
"customfield_12102",
"customfield_12105",
"customfield_14401",
"customfield_12217",
"customfield_12216",
"customfield_11008",
"customfield_13300",
"customfield_12211",
"customfield_11002",
"customfield_12213",
"customfield_12212",
"customfield_11003",
"customfield_12215",
"customfield_12214",
"customfield_12206",
"customfield_12205",
"customfield_12208",
"customfield_10700",
"customfield_12207",
"customfield_10701",
"customfield_12209",
"resolutiondate",
"customfield_10020",
"customfield_12200",
"customfield_10021",
"customfield_12202",
"customfield_12201",
"customfield_12204",
"customfield_14500",
"customfield_12203",
"customfield_14501",
"customfield_10016",
"customfield_10017",
"customfield_10810",
"customfield_10018",
"customfield_10019",
"updated" AS "updated_u1",
"timeoriginalestimate",
"description" AS "description_u9",
"customfield_11100",
"customfield_10011",
"customfield_11101",
"customfield_10012",
"customfield_13400",
"customfield_10014",
"customfield_10015",
"customfield_10007",
"customfield_10801",
"customfield_10803",
"customfield_10806",
"customfield_10807",
"customfield_10808",
"customfield_10809",
"summary" AS "summary_u1",
"customfield_10000",
"customfield_10001",
"customfield_12300",
"customfield_10004",
"customfield_10115",
"duedate",
"customfield_10110",
"customfield_10111",
"customfield_11200",
"customfield_13500",
"customfield_11201",
"customfield_10114",
"customfield_14825",
"customfield_14823",
"customfield_14824",
"customfield_10900",
"customfield_10109",
"customfield_12400",
"customfield_14821",
"customfield_14700",
"customfield_14822",
"customfield_14820",
"customfield_12401",
"customfield_14814",
"customfield_14815",
"customfield_14812",
"customfield_14813",
"timeestimate",
"customfield_14818",
"customfield_14819",
"customfield_14816",
"customfield_14817",
"customfield_14810",
"customfield_14811",
"customfield_11301",
"customfield_11302",
"customfield_13600",
"customfield_14804",
"customfield_14801",
"customfield_14009",
"customfield_14802",
"aggregatetimeestimate",
"customfield_14807",
"customfield_14808",
"customfield_14809",
"customfield_14000",
"customfield_14003",
"customfield_14001",
"customfield_14002",
"customfield_14008",
"customfield_14005",
"customfield_14006",
"customfield_13710",
"customfield_10202",
"customfield_12500",
"customfield_13702",
"customfield_13701",
"customfield_13704",
"customfield_13703",
"customfield_13706",
"customfield_13705",
"customfield_13708",
"customfield_13707",
"customfield_13709",
"timespent",
"aggregatetimespent",
"customfield_13700",
"customfield_12602",
"customfield_12601",
"workratio",
"created" AS "created_u2",
"customfield_14100",
"customfield_12600",
"customfield_13801",
"customfield_13800",
"customfield_13001",
"customfield_13000",
"customfield_13002",
"customfield_11500",
"customfield_12700",
"security",
"customfield_14201",
"customfield_14200",
"customfield_10511",
"customfield_13900",
"customfield_10513",
"customfield_10514",
"customfield_10517",
"customfield_10100",
"assignee",
"customfield_10022",
"__KEY_fields",
"__FK_fields",
(SELECT
"id" AS "id_u10",
// "self" AS "self_u10",
"__KEY_issuelinks",
"__FK_issuelinks",
(SELECT
"id",
"name",
"inward",
"outward",
// "self",
"__FK_type"
FROM "type" FK "__FK_type"),
(SELECT
"id" AS "id_u4",
"key" AS "key_u0",
// "self" AS "self_u4",
"__KEY_inwardIssue",
"__FK_inwardIssue",
(SELECT
"summary",
"__KEY_fields_u0",
"__FK_fields_u0",
(SELECT
// "self" AS "self_u1",
"description",
"iconUrl",
"name" AS "name_u1",
"id" AS "id_u1",
"__KEY_status",
"__FK_status",
(SELECT
// "self" AS "self_u0",
"id" AS "id_u0",
"key",
"colorName",
"name" AS "name_u0",
"__FK_statusCategory"
FROM "statusCategory" FK "__FK_statusCategory")
FROM "status" PK "__KEY_status" FK "__FK_status"),
(SELECT
// "self" AS "self_u2",
"iconUrl" AS "iconUrl_u0",
"name" AS "name_u2",
"id" AS "id_u2",
"__FK_priority"
FROM "priority" FK "__FK_priority"),
(SELECT
// "self" AS "self_u3",
"id" AS "id_u3",
"description" AS "description_u0",
"iconUrl" AS "iconUrl_u1",
"name" AS "name_u3",
"subtask",
"avatarId",
"__FK_issuetype"
FROM "issuetype" FK "__FK_issuetype")
FROM "fields" PK "__KEY_fields_u0" FK "__FK_fields_u0")
FROM "inwardIssue" PK "__KEY_inwardIssue" FK "__FK_inwardIssue"),
(SELECT
"id" AS "id_u9",
"key" AS "key_u2",
// "self" AS "self_u9",
"__KEY_outwardIssue",
"__FK_outwardIssue",
(SELECT
"summary" AS "summary_u0",
"__KEY_fields_u1",
"__FK_fields_u1",
(SELECT
// "self" AS "self_u6",
"description" AS "description_u1",
"iconUrl" AS "iconUrl_u2",
"name" AS "name_u5",
"id" AS "id_u6",
"__KEY_status_u0",
"__FK_status_u0",
(SELECT
// "self" AS "self_u5",
"id" AS "id_u5",
"key" AS "key_u1",
"colorName" AS "colorName_u0",
"name" AS "name_u4",
"__FK_statusCategory_u0"
FROM "statusCategory" FK "__FK_statusCategory_u0")
FROM "status" PK "__KEY_status_u0" FK "__FK_status_u0"),
(SELECT
// "self" AS "self_u7",
"iconUrl" AS "iconUrl_u3",
"name" AS "name_u6",
"id" AS "id_u7",
"__FK_priority_u0"
FROM "priority" FK "__FK_priority_u0"),
(SELECT
// "self" AS "self_u8",
"id" AS "id_u8",
"description" AS "description_u2",
"iconUrl" AS "iconUrl_u4",
"name" AS "name_u7",
"subtask" AS "subtask_u0",
"avatarId" AS "avatarId_u0",
"__FK_issuetype_u0"
FROM "issuetype" FK "__FK_issuetype_u0")
FROM "fields" PK "__KEY_fields_u1" FK "__FK_fields_u1")
FROM "outwardIssue" PK "__KEY_outwardIssue" FK "__FK_outwardIssue")
FROM "issuelinks" PK "__KEY_issuelinks" FK "__FK_issuelinks"),
(SELECT
// "self" AS "self_u11",
"name" AS "name_u8",
"key" AS "key_u3",
// "accountId",
"emailAddress",
"displayName",
"active",
"timeZone",
"__KEY_assignee",
"__FK_assignee"
// (SELECT
// "48x48",
// "24x24",
// "16x16",
// "32x32",
// "__FK_avatarUrls"
// FROM "avatarUrls" FK "__FK_avatarUrls")
FROM "assignee" PK "__KEY_assignee" FK "__FK_assignee"),
(SELECT
// "self" AS "self_u12",
"id" AS "id_u11",
"name" AS "name_u9",
"description" AS "description_u3",
"__FK_components"
FROM "components" FK "__FK_components"),
(SELECT
"id" AS "id_u12",
"value",
"__FK_customfield_10600"
FROM "customfield_10600" FK "__FK_customfield_10600"),
(SELECT
"@Value",
"__FK_subtasks"
FROM "subtasks" FK "__FK_subtasks" ArrayValueAlias "@Value"),
(SELECT
// "self" AS "self_u13",
"name" AS "name_u10",
"key" AS "key_u4",
// "accountId" AS "accountId_u0",
"emailAddress" AS "emailAddress_u0",
"displayName" AS "displayName_u0",
"active" AS "active_u0",
// "timeZone" AS "timeZone_u0",
"__KEY_reporter",
"__FK_reporter"
// (SELECT
// "48x48" AS "48x48_u0",
// "24x24" AS "24x24_u0",
// "16x16" AS "16x16_u0",
// "32x32" AS "32x32_u0",
// "__FK_avatarUrls_u0"
// FROM "avatarUrls" FK "__FK_avatarUrls_u0")
FROM "reporter" PK "__KEY_reporter" FK "__FK_reporter"),
(SELECT
"progress",
"total",
"percent",
"__FK_progress"
FROM "progress" FK "__FK_progress"),
(SELECT
// "self" AS "self_u14",
"votes",
"hasVoted",
"__FK_votes"
FROM "votes" FK "__FK_votes"),
(SELECT
"startAt",
"maxResults",
"total" AS "total_u0",
"__KEY_worklog",
"__FK_worklog",
(SELECT
// "self" AS "self_u17",
"comment",
"created",
"updated",
"started",
"timeSpent",
"timeSpentSeconds",
"id" AS "id_u13",
"issueId",
"__KEY_worklogs",
"__FK_worklogs",
(SELECT
// "self" AS "self_u15",
"name" AS "name_u11",
"key" AS "key_u5",
// "accountId" AS "accountId_u1",
"emailAddress" AS "emailAddress_u1",
"displayName" AS "displayName_u1",
"active" AS "active_u1",
// "timeZone" AS "timeZone_u1",
"__KEY_author",
"__FK_author"
// (SELECT
// "48x48" AS "48x48_u1",
// "24x24" AS "24x24_u1",
// "16x16" AS "16x16_u1",
// "32x32" AS "32x32_u1",
// "__FK_avatarUrls_u1"
// FROM "avatarUrls" FK "__FK_avatarUrls_u1")
FROM "author" PK "__KEY_author" FK "__FK_author"),
(SELECT
// "self" AS "self_u16",
"name" AS "name_u12",
"key" AS "key_u6",
// "accountId" AS "accountId_u2",
"emailAddress" AS "emailAddress_u2",
"displayName" AS "displayName_u2",
"active" AS "active_u2",
// "timeZone" AS "timeZone_u2",
"__KEY_updateAuthor",
"__FK_updateAuthor"
// (SELECT
// "48x48" AS "48x48_u2",
// "24x24" AS "24x24_u2",
// "16x16" AS "16x16_u2",
// "32x32" AS "32x32_u2",
// "__FK_avatarUrls_u2"
// FROM "avatarUrls" FK "__FK_avatarUrls_u2")
FROM "updateAuthor" PK "__KEY_updateAuthor" FK "__FK_updateAuthor")
FROM "worklogs" PK "__KEY_worklogs" FK "__FK_worklogs")
FROM "worklog" PK "__KEY_worklog" FK "__FK_worklog"),
(SELECT
// "self" AS "self_u18",
"id" AS "id_u14",
"description" AS "description_u4",
"iconUrl" AS "iconUrl_u5",
"name" AS "name_u13",
"subtask" AS "subtask_u1",
"avatarId" AS "avatarId_u1",
"__FK_issuetype_u1"
FROM "issuetype" FK "__FK_issuetype_u1"),
(SELECT
// "self" AS "self_u20",
"id" AS "id_u16",
"key" AS "key_u7",
"name" AS "name_u15",
"projectTypeKey",
"__KEY_project",
"__FK_project",
// (SELECT
// "48x48" AS "48x48_u3",
// "24x24" AS "24x24_u3",
// "16x16" AS "16x16_u3",
// "32x32" AS "32x32_u3",
// "__FK_avatarUrls_u3"
// FROM "avatarUrls" FK "__FK_avatarUrls_u3"),
(SELECT
// "self" AS "self_u19",
"id" AS "id_u15",
"description" AS "description_u5",
"name" AS "name_u14",
"__FK_projectCategory"
FROM "projectCategory" FK "__FK_projectCategory")
FROM "project" PK "__KEY_project" FK "__FK_project"),
(SELECT
"@Value" AS "@Value_u0",
"__FK_customfield_10702"
FROM "customfield_10702" FK "__FK_customfield_10702" ArrayValueAlias "@Value_u0"),
(SELECT
// "self" AS "self_u21",
"watchCount",
"isWatching",
"__FK_watches"
FROM "watches" FK "__FK_watches"),
(SELECT
"id" AS "id_u17",
"value" AS "value_u0",
"__FK_customfield_10022"
FROM "customfield_10022" FK "__FK_customfield_10022"),
(SELECT
"originalEstimate",
"remainingEstimate",
"originalEstimateSeconds",
"remainingEstimateSeconds",
"timeSpent" AS "timeSpent_u0",
"timeSpentSeconds" AS "timeSpentSeconds_u0",
"__FK_timetracking"
FROM "timetracking" FK "__FK_timetracking"),
(SELECT
"@Value" AS "@Value_u1",
"__FK_customfield_10006"
FROM "customfield_10006" FK "__FK_customfield_10006" ArrayValueAlias "@Value_u1"),
(SELECT
"maxResults" AS "maxResults_u0",
"total" AS "total_u1",
"startAt" AS "startAt_u0",
"__KEY_comment",
"__FK_comment",
(SELECT
// "self" AS "self_u24",
"id" AS "id_u18",
"body",
"created" AS "created_u0",
"updated" AS "updated_u0",
"__KEY_comments",
"__FK_comments",
(SELECT
// "self" AS "self_u22",
"name" AS "name_u16",
"key" AS "key_u8",
// "accountId" AS "accountId_u3",
"emailAddress" AS "emailAddress_u3",
"displayName" AS "displayName_u3",
"active" AS "active_u3",
// "timeZone" AS "timeZone_u3",
"__KEY_author_u0",
"__FK_author_u0"
// (SELECT
// "48x48" AS "48x48_u4",
// "24x24" AS "24x24_u4",
// "16x16" AS "16x16_u4",
// "32x32" AS "32x32_u4",
// "__FK_avatarUrls_u4"
// FROM "avatarUrls" FK "__FK_avatarUrls_u4")
FROM "author" PK "__KEY_author_u0" FK "__FK_author_u0"),
(SELECT
// "self" AS "self_u23",
"name" AS "name_u17",
"key" AS "key_u9",
// "accountId" AS "accountId_u4",
"emailAddress" AS "emailAddress_u4",
"displayName" AS "displayName_u4",
"active" AS "active_u4",
// "timeZone" AS "timeZone_u4",
"__KEY_updateAuthor_u0",
"__FK_updateAuthor_u0"
// (SELECT
// "48x48" AS "48x48_u5",
// "24x24" AS "24x24_u5",
// "16x16" AS "16x16_u5",
// "32x32" AS "32x32_u5",
// "__FK_avatarUrls_u5"
// FROM "avatarUrls" FK "__FK_avatarUrls_u5")
FROM "updateAuthor" PK "__KEY_updateAuthor_u0" FK "__FK_updateAuthor_u0")
FROM "comments" PK "__KEY_comments" FK "__FK_comments")
FROM "comment" PK "__KEY_comment" FK "__FK_comment"),
(SELECT
// "self" AS "self_u25",
"id" AS "id_u19",
"description" AS "description_u6",
"name" AS "name_u18",
"archived",
"released",
"releaseDate",
"__FK_fixVersions"
FROM "fixVersions" FK "__FK_fixVersions"),
(SELECT
// "self" AS "self_u26",
"value" AS "value_u1",
"id" AS "id_u20",
"__FK_customfield_10100"
FROM "customfield_10100" FK "__FK_customfield_10100"),
(SELECT
// "self" AS "self_u27",
"iconUrl" AS "iconUrl_u6",
"name" AS "name_u19",
"id" AS "id_u21",
"__FK_priority_u1"
FROM "priority" FK "__FK_priority_u1"),
(SELECT
// "self" AS "self_u29",
"description" AS "description_u7",
"iconUrl" AS "iconUrl_u7",
"name" AS "name_u21",
"id" AS "id_u23",
"__KEY_status_u1",
"__FK_status_u1",
(SELECT
// "self" AS "self_u28",
"id" AS "id_u22",
"key" AS "key_u10",
"colorName" AS "colorName_u1",
"name" AS "name_u20",
"__FK_statusCategory_u1"
FROM "statusCategory" FK "__FK_statusCategory_u1")
FROM "status" PK "__KEY_status_u1" FK "__FK_status_u1"),
(SELECT
// "self" AS "self_u30",
"name" AS "name_u22",
"key" AS "key_u11",
// "accountId" AS "accountId_u5",
"emailAddress" AS "emailAddress_u5",
"displayName" AS "displayName_u5",
"active" AS "active_u5",
// "timeZone" AS "timeZone_u5",
"__KEY_creator",
"__FK_creator"
// (SELECT
// "48x48" AS "48x48_u6",
// "24x24" AS "24x24_u6",
// "16x16" AS "16x16_u6",
// "32x32" AS "32x32_u6",
// "__FK_avatarUrls_u6"
// FROM "avatarUrls" FK "__FK_avatarUrls_u6")
FROM "creator" PK "__KEY_creator" FK "__FK_creator"),
(SELECT
"progress" AS "progress_u0",
"total" AS "total_u2",
"percent" AS "percent_u0",
"__FK_aggregateprogress"
FROM "aggregateprogress" FK "__FK_aggregateprogress"),
(SELECT
// "self" AS "self_u32",
"id" AS "id_u24",
"filename",
"created" AS "created_u1",
"size",
"mimeType",
"content",
"thumbnail",
"__KEY_attachment",
"__FK_attachment",
(SELECT
// "self" AS "self_u31",
"name" AS "name_u23",
"key" AS "key_u12",
// "accountId" AS "accountId_u6",
"emailAddress" AS "emailAddress_u6",
"displayName" AS "displayName_u6",
"active" AS "active_u6",
// "timeZone" AS "timeZone_u6",
"__KEY_author_u1",
"__FK_author_u1"
// (SELECT
// "48x48" AS "48x48_u7",
// "24x24" AS "24x24_u7",
// "16x16" AS "16x16_u7",
// "32x32" AS "32x32_u7",
// "__FK_avatarUrls_u7"
// FROM "avatarUrls" FK "__FK_avatarUrls_u7")
FROM "author" PK "__KEY_author_u1" FK "__FK_author_u1")
FROM "attachment" PK "__KEY_attachment" FK "__FK_attachment"),
(SELECT
// "self" AS "self_u33",
"value" AS "value_u2",
"id" AS "id_u25",
"__FK_customfield_10500"
FROM "customfield_10500" FK "__FK_customfield_10500"),
(SELECT
// "self" AS "self_u34",
"value" AS "value_u3",
"id" AS "id_u26",
"__FK_customfield_10506"
FROM "customfield_10506" FK "__FK_customfield_10506"),
(SELECT
// "self" AS "self_u35",
"id" AS "id_u27",
"description" AS "description_u8",
"name" AS "name_u24",
"__FK_resolution"
FROM "resolution" FK "__FK_resolution"),
(SELECT
// "self" AS "self_u36",
"value" AS "value_u4",
"id" AS "id_u28",
"__FK_customfield_10109"
FROM "customfield_10109" FK "__FK_customfield_10109")
FROM "fields" PK "__KEY_fields" FK "__FK_fields")
FROM "issues" PK "__KEY_issues" FK "__FK_issues")
FROM JSON (wrap on) "root" PK "__KEY_root";
Many thanks,
Christopher.
The JQL that can be embedded in the right kind of Jira REST API calls can be learned here:
You should strive to limit the data as early as possible in your data logistics chain. That is especially true for REST API based sources. Since REST is rather in-efficient and slow compared to database/table oriented sources.
So try to get the REST API call to include the limits you need. Jira has what is called JQL, search and filters that could be used for limiting the data: JIRA 7.6.1
The SELECT in itself is a proprietary kind of rather simplistic but hierarchy aware SELECT which does not support a WHERE clause. Have a look here:
If you can't limit in the REST API call you can do a resident load after the SELECT in your load script and then in the LOAD statement have a WHERE clause. But that is as previously mentioned very inefficient - at least with larger amounts of data.
The JQL that can be embedded in the right kind of Jira REST API calls can be learned here:
Maybe a third-party Jira ODBC connector could be worth the investment instead of handling all the intricacies of REST:
ODBC Driver for Jira | Atlassian Marketplace
Simba: https://www.simba.com/drivers/jira-odbc-jdbc/
CDATA: JIRA ODBC Driver - ODBC | ODBC Drivers | ODBC Connection | ODBC API
Would i be able to put my search criteria in the URL section of the REST Connector?
Currently i am filtering on Project and it works.
How do i do the same for the "created" field?
https://xxxxxxxxxxxxxxx/rest/api/2/search?fields=*all&jql=project%20in%20("Business%20As%20Usual")
You just do an AND just as shown in the link I gave you to the documentation.
If the JQL string gets too long you can do a POST request - also mentioned in the documentation.
Instead of adding it to the URL property you could add the query parameters it the WITH CONNECTION clause of the SELECT statement in the load script.
Thank you so much! The documentation you shared with me pointed me in the right direction.
Much appreciated!
Could you please share your end solution?
hi Valeriya,
could you find any solution?
thank you,
Hello! I used the same way as mentioned above - filtering data via jql - it is the only way which worked for me
CUSTOM CONNECT TO "Provider=QvRestConnector.exe;url=https://jira.XXXXXXXXX.ru/rest/api/2/search?jql%2project%2%3CXXXXXXXX%3&fields%2*all;timeout=30;method=GET;
sendExpect100Continue=True;autoDetectResponseType=true;checkResponseTypeOnTestConnection=true;keyGenerationStrategy=0;authSchema=basic;skipServerCertificateValidation=false;
useCertificate=No;certificateStoreLocation=CurrentUser;certificateStoreName=My;
PaginationType=Offset;OffsetStartField=startAt;IsOffsetStartFieldHeader=0;OffsetStartFieldValue=0;
OffsetCountFieldName=maxResults;IsOffsetCountFieldHeader=0;OffsetCountValue=1000;OffsetTotalPath=total;IsOffsetTotalPathHeader=0;OffsetDataPath=root/total;
allowResponseHeaders=1;allowHttpsOnly=false;XUserId=WBRXWQZFCLYEHadNTDaOO;XPassword=VQWDXSUEQLYGXZdOPTcIWSIGTabKXcEMTJMATSEGPB;";