In previous video, we successfully pull issue info in JSON format. This time we'll learn how to use JQL for advance queries
In JIRA, if we type any search, it will take you to the search page which allow you to browse the result or specify more search criteria
At the search bar, you can see that there are many dropdowns box to let you choose criteria combination
Now, if you click the "Advance" button, it'll show you the JQL (JIRA Query Language) that represent the current search criteria
To search issues with JQL via REST API, simply copy the JQL from the search bar and append to the end of the API URL "rest/api/2/search?jql=xxxxxxxxx" (xxx is a JQL string)
That's it! Now we've got the search result in JSON format!
For more complex query, I'd be a good idea to build the JQL string in the JIRA search page and then copy to the API url
For example, I want to query the issue by "status" and "assignee"
With this method, you don't have to worry about the syntax.
Now we know the basic. Let's try searching via Excel VBA
I'll use the sheet from previous video (in the playlist) that we pulled the issue from REST API via VBA.
I'm going to change the HTML Request string with our JQL and see what happen
You'll see that's there is a compile error because we have double quotes in the JQL string. To solve this, you have to encode your HTML string
To do this, just copy the url from the browser, like this.
You'll see that all special characthers were all encoded automatically!
Now the next step, I'm going to put some of the search result to the Excel sheet. you can learn how to parse JSON data in previous video in the playlist!
First, I'm going to put only first 10 results so let's make a loop from 1 to 10
Then, I'm going to put the issue key in the first column of the sheet
Noted that the search result will be returned in array name "issues"
OK, let's run the code!
It works!
The important final note is that, there is a limit of returned result. This was setup by the JIRA and cannot be changed by request string
For this tutorial, You'll that "startAt" is 0 and "maxResult" is 50 which means that the API will return only 50 first result no matter what
Luckily, our result only have 34 issues (see field "total") so that won't be a problem for now. However, we'll learn how to handle that in the next video when your search result is expected to return lots of result!
Thank you for watching! Subscribe for more tips and tutorial!!