In this video, we will discuss JQL. We will start with a JQL overview. In the previous video, we explored basic search. In this example, we are searching for all issues that we have permission to view in project A. On a basic search, there is a switch to JQL link, which allows you to switch to the advanced mode of searching for issues. Here is advanced or JQL search, Jira will automatically populate the textbox, with a text-based equivalent for the current basic search. You can click the switch to basic link to change back to the basic search. That text that we saw in the advanced issue search is called Jira query language or JQL. JQL uses a structured query language like syntax. Structured query language is commonly called SQL or Sequel. This is used to search databases. JQL is used to search issues only. This is why the JQL string can be so simple. You don't have to enter something like from issue in the query because that is implied. We have seen that the basic search contains user-friendly interface elements to perform the search. We have also seen that the queries can be quite complex by selecting from multiple elements or adding text-based searching. Advanced search uses JQL and allows for more powerful searches because it is not limited to the capabilities of the user interface elements of basic search. Also, if you are automating anything related to Jira, you would use JQL because automation relies on text-based scripts. Whether you are using basic or advanced search, the JQL related to the search is always there behind the scenes. The easiest way to write the JQL query is not to write it at all. You can let Jira write it for you. Here we are in basic search and select Project A from the dropdown. We can then click on the Advanced link" to enter the advanced search. The search results don't change at all, but the query is displayed instead of the basic search user interface. That query displays all of the issues with a project key of PROJ. The results are ordered by the date that the issue was created. JQL queries can fundamentally do two things and we see both of them in the query that was just created. They can select a subset of issues, as we see in the first part of the query. We are selecting only issues in which the project field has a value of PROJ. They can sort the results using an ORDER By clause. We are ordering the results by the date that they were created. Next, we will discuss autocomplete. We have the option of creating a JQL query from scratch. Let's create a query that searches for the issues in our project. Project is a field name in a Jira issue, and each issue is assigned to a single project. In the Advanced Search textbox, we can begin typing pr and Jira will help us with autocomplete suggestions. Autocomplete will show up to 15 matches, this makes writing JQL much easier and helps avoid mistakes. In this case, we select the project field as the start to the query. Notice that there is a syntax help option at the bottom of the list, this will point you to the documentation for advanced search. When we have entered project in the search box and hit the spacebar, Jira provides a list of choices of what should be next in the query. This list contains what are called operators, and since we are looking for issues in our particular project, we select the equals operator. When you press the spacebar after entering the equals operator, Jira again provides you with a list of choices. We can see that this Jira account has three projects that we have permission to see, and we can select a project name to view these issues. When we complete the query and click "Enter", the search is performed. At this point, if we click on the basic link, we can see that project A is selected in the dropdown list. This is equivalent to the JQL that we just created. If we click on the updated column in the search results, the results are sorted and we can see that our query has been updated with an ORDER By clause. This is very helpful in creating JQL queries. You can create a search in basic search, change the output to be sorted the way that you would like, and then copy the query that Jira has created. You usually don't have to create queries from scratch. In the previous example, we knew that we were searching for the project field. If you are unsure of which field to search for, or to get more information related to the field, such as supported operators and functions, search the web for the advanced searching-fields reference documentation from Atlassian. Next we will discuss Order By. The simplest JQL is an empty string as shown here. This means that we are not being selective about which issues that we see, so we see them all. You can see that there are 29 issues in this Jira account, assuming that the current user has permission to see all of the issues. We don't specify how we would like the results to be ordered, so Jira picks the default ordering. It looks like the default ordering is by the issue key in a descending order. Order By in a JQL query is used to specify the order of the results. In this simple case of an empty string as a query, as we see on the left, all of the issues are displayed in the default order. We guessed that the default sorting is by issue key in a descending order. On the right, we verify that guess by specifically adding an order by clause to the query. You can see that if we specify Order By key descending, we get the same results as we did with the empty query. The only real difference is that on the right, you see a descending arrow on the key column header. If you click on the All Issues link under Filters, you can see that the query is simply an Order By clause. The results are ordered by the date and time that the issue was created, starting with the most recently created issue. You can also see that the created column header has a descending arrow. Clicking on any column header will change the Order By clause in the query. You can add multiple field names to the Order By clause by separating them with commas. The first field name is the primary sorting field, and any other fields take effect only if the prior field had multiple values that were exactly the same. In this example, we are sorting primarily by the summary field because it is first in the Order By clause, and because you can see that the summary column has the sorting arrow. We have multiple issues with the exact same summary, so the secondary field of the issue key is used to sort those matches. As you click on multiple columns in the search results, you will notice that more fields are added to the Order By clause. The first field is by far the most important one. Next, we will discuss Functions. You usually only want to return a subset of issues from a search. An issue selection clause is used to limit or filter the issues that are returned in the results. The basic structure of the clause is a field name followed by an operator, followed by a field value. We have seen an example where we are selecting all issues that have a project field value of project A. Instead of directly providing a field value in the issue selection clause, you can provide a function. A function is a small program that Jira calls before the query is executed. The result of calling the function is then substituted in the query. As an example, this clause will search for issues that have been assigned to the currently logged in JIRA user. A big advantage of using functions is that you are not hard coding information. This same query can be used by all users to view their issues. The Jira advanced searching functions are documented so that you can use them if and when you need them. Do a search for advanced searching functions reference to find it on Atlassian's website. You can then find the details related to any functions that interest you. Here's a list of the advanced search functions that are related to time and date. There are functions that specify the start of a time period and the end of a time period. The now function represents the current time. The current login function results in the time that the current user logged into the session, and the last login function results in the time that the current user logged into the previous session. As an example, this query will find issues created since the start of today. If the field is related to dates, you can add what's called a time unit qualifier to specify relative dates as field values. This is a specially formatted string that Jira will replace with an actual date value before running the query. In this example, we are searching for issues created in the last two days. The time unit qualifier is handy because it is simple and the query doesn't have a hard-coded date. This line shows the syntax that can be used to specify time units. It starts with a plus sign, a vertical bar, and a minus sign in parentheses. The parentheses mean that what is inside is optional. The vertical bar means or. If you add a minus sign to the string, you are searching back in time. Using the plus sign or leaving off the sign altogether means that you are searching forward in time. You can see that this query uses the minus sign. So this query is searching for issues that were created two days ago or later. The nn in the string represents numeric digits. In this string, the numeric digit is two. Following the numeric digits is the optional time period unit. You can specify y for year, capital M for month, w for week, d for day, h for hour, and lowercase m for a minute. In this case we specify d. So this query is searching for issues created within the last two days. We could change that d to a w, for example, to search for issues that were created in the past two weeks. If you leave off the units, Jira will assume a logical default unit, which depends on the circumstances. You can also use time unit qualifiers as arguments to a function. An argument is a value that is passed to functions to change its behavior. If the trailing parenthesis of a function call are empty, you are not passing any arguments and the function is called with its default behavior. In this example, we are adding an argument to change the function so that we are searching for issues created since the start of day two days ago. If today is Wednesday, this would be searching for issues created Monday or later. Here's an example of finding issues created since the 15th of this month, assuming we are currently in the second half of the month. Notice that the function is related to months, but we are passing an argument related to days. The time periods do not need the match. Next, we will discuss operators. An operator is placed between the field name and the value. In this example, the operator is the equal sign. You can use autocomplete to see which operators that you can use for a specific field name. In this example, we have entered the "project" field name and then pressed the space bar. Autocomplete provides a list of choices for the operators. In this example, we have entered the "summary" field name and then pressed the space bar. Notice that Jira provides a different list of operators for the "summary" field. For example, the "project" field accepts the equals sign as an operator, and the "summary" field accepts the contains operator which is represented by a tilde. Jira's autocomplete provides the acceptable operators based on the expected type of field value. The "project" field expects a project name and the summary field expects a free form of text. The advanced searching operators reference documents the details related to operators. We will briefly go over operators in this video, but this is mostly to provide you awareness of what is possible with operators in queries. The operators reference is very helpful as you are writing queries. The equals operator will find issues where the field value exactly matches the value in the query. This query will find all issues in "projectA." The not equals operator represented by an exclamation point before the equal sign, finds issues where the field value does not exactly match the value in the query. This query finds all issues that are not in the "projectA" project. Some fields allow the greater than, greater than or equal, less than and less than or equals operators. For example, these are commonly used along with fields that expect values of dates. This query finds issues created today. This query uses the greater than or equals operator to find issues with three or more "Story Points." Notice that we have to surround the field name in quotes because it contains a space. This query finds issues created more than a week ago. This query finds issues with three or less "Story Points." The "in" and "not in" operators are used with a comma separated list of values in the query. This query finds issues that are either in "projectA" or "projectB." Notice that there are parentheses around the set of values and the values are separated using commas. This query finds issues that are not in "projectA" or "projectB." The "is" and "is not" operators are very limited in what they do. They are used to determine if a value has been set for a field. These operators can only be followed by one of two values, empty or null. Both values are keywords in JQL. They mean the same thing. In this example, we find issues that have no assignee. We do this first using the "is" operator, which is equivalent to the equals operator. In this example, we find issues that haven't assignee using the "is not" operator. This query is equivalent to a query that uses the "not equals" operator. The "contains" and "does not contain" operators are used with text fields only. Text fields include summary, description, environment, comments, and any custom text fields. In this example, our field is "summary," and since it is a text field, the "contains" and "does not contain" operators are offered as options. This query finds issues in which the "summary" field has a value of "item 1." We put quotes around the value because values with spaces must be surrounded by quotes. This query finds all issues where there's summary does not contain the word "item." Quotes are not required because this is a single string of simple text. You can perform a search that is similar to the global search using a special name of text and the "contains" operator. You can see that you can use the same text query syntax that we discussed for quick search earlier. Note that the capital not inside the string is case sensitive. A lowercase "not" is a reserved word and is ignored in the search. The keywords in these text fields searches are an exception to the general rule that Jira searches are case insensitive. The "was" and "was not" operators are used to find issues that previously had a value. Since Jira keeps a historic record of events and values, that history can be searched using these operators. In this example, we find issues where the current user has been assigned. In this example, we find issues that have never had a status of "In Progress." The was in and was not in operators are similar to the was and was not operators, but applied to a set of values rather than a single value. This query finds issues that had one of two statuses. Either "Selected for Development" or "In Progress". This query uses the was not in operator to find issues that have never had one of those two statuses. The operators with the word "was" in them can have a predicate appended to the clause. Predicates are used to narrow the search results from the clause. Here is the list of predicates for these operators. The AFTER, BEFORE, DURING, and ON, predicates are used to limit the date range of the query. The BY predicate is used to specify the user performing the change. This example finds all issues that were moved to the Done status by the current user in the past month. Notice that predicates can be combined, as we see here with the BY and AFTER predicates. The final operator that we will discuss is the changed operator. This is used to find issues that have a field value that has changed. For example, this query finds issues whose assignee has changed since the issue was created. The changed operator also has predicates that can be added in order to narrow the returned issues. In addition to the predicates that we saw related to the was operators, the changed operator adds FROM and TO predicates. This example finds issues whose status changed from "Done" to "In Progress" at some point in their history. Next, we will discuss Boolean operators. Boolean operators are used to either combine or negate clauses. The AND and OR Boolean operators are used to combine multiple clauses, allowing you to refine your search. For example, this query finds all issues assigned to the current user with the status of "In Progress". This query finds all issues with the status of "Selected for Development" or "In Progress". This query is equivalent to using the IN operator. The NOT operator is used to negate one or more clauses. For example, this query finds issues that do not have a status of Backlog. This is equivalent to using the NOT equals operator. In this example, we find unresolved issues in all projects except for the SampleA project. Notice that we are combining AND and NOT Boolean operators. You could do the same with OR and NOT Boolean operators. If your query has multiple Boolean operators, use parentheses to set the operator precedence and improve query readability. In this example, we have added parentheses, so the OR Boolean operator is evaluated first. The result is then ended with the first clause. This query searches for all issues with the status of "Selected for Development" or "In Progress", with the summary containing the text item. You can see that this results in eight issues. If we remove the parentheses, the AND Boolean operator is evaluated first. In this example, we are searching for issues containing the text item with the status of "Selected for Development" or any issue that has the status of "In Progress". You can see that the results include one more issue than the previous search, because this query will include any issues with the status of "In Progress", even if the word "item" is not in the summary. If your query combines the use of AND and OR Boolean operators, there are two precedence rules. Parentheses take precedence, meaning that what is inside of parentheses is evaluated first. The AND operator normally has precedence over OR. This means that if there are no parentheses to set precedence, the AND operators are evaluated first from left to right. This is analogous to math, where multiplication has precedence over addition, unless parentheses changed the precedence. Here's an example. This query contains an OR as well as an AND Boolean operator. Since there are no parentheses used to change the precedence, the AND is evaluated first because AND has precedence over OR. This query is equivalent to the previous query. Putting parenthesis around the AND operator means that AND is evaluated first, but it already was evaluated first in this query, so the queries are equivalent. A general tip is to use parenthesis and queries with mixed operators to keep things simple and unambiguous. Here's a review of what we've discussed in this video. A JQL query is behind all basic and advanced searches. Leverage basic queries and autocomplete to simplify creating JQL queries. JQL queries may select subsets of issues and/or order query results. Functions can be used to avoid hard-coding values in a clause. Time unit qualifiers can be used with date-related fields. Allowable operators depend on the type of field value. Use operator autocomplete to simplify writing clauses. Boolean operators are used to combine or negate clauses. Now it's time for you to work on some of the things that we've discussed in this video. Separate hands-on instructions are provided for you.
