On this page, we’ll explain how Advanced Query Language (AQL) queries are executed in Jira Service Management and tips to optimize your AQL queries. For the examples described on this page, let’s assume you have three object schemas.

How are AQL queries executed?

AQL queries are executed in three phases:

1. Scoping: This phase returns potential result candidates based on extremely easy to determine clauses by scoping out large chunks of data that don’t match your query. 
   Some examples of basic clauses include: objectSchema, objectSchemaId, objectType, objectTypeId, Id, or Key.

2. Predicate: This phase generates a function (predicate) from the AQL query, which is used to run against objects and determine valid results in the testing phase. This phase gets more complex when the query contains inbound or outbound references, or connected tickets, especially when inbound or outbound references contain a nested AQL query and connected tickets contain a nested JQL query.
   Let’s take a look at how some queries and how they’re converted.
   Example 1:
   Query:

   objectSchema = SMALL AND Name == "Sydney Office"

   is converted into the following pseudocode:

   return (object.objectSchema.name == "SMALL" && object.name == "Sydney Office")

   Example 2:
   Query:

   object NOT HAVING connectedTickets() OR object NOT HAVING outboundReferences()

   is converted into the following pseudocode:

   return (!connectedTicketsIndex.contains(object.id) || object.outboundReferences().isEmpty())

   Example 3:
   Query:

   (objectSchema = SMALL OR objectType = Employees) AND Label LIKE "John" AND (object HAVING connectedTickets() OR object HAVING inboundReferences())

   is converted into the following pseudocode:

   return (object.objectSchema.name == "SMALL" || object.objectType.name == "Employees") && object.label.contains("John") && (connectedTicketsIndex.contains(object.id) || object.inboundReferences.isNotEmpty())

3. Testing: Runs all objects determined from the scoping phase against the expression generated in the predicate phase to return actual results. 
   In the Example 1 above, the scope of the query is limited to object IDs (say IDs 1,000,001 to 1,000,050) from the object schema SMALL are tested individually using the below logic:

   return (object.objectSchema.name == "SMALL" && object.name == "Sydney Office")

   If you’ve provided a pagination limit, this step is performed until the desired number of results is reached.
   In the Example 3 above, the scope of the query is object types (say IDs 1,000,001 to 1,000,050) in the object schema SMALL and the object type (say IDs 10,000 to 20,000)  Employees from the object schema LARGE are tested individually using the below logic until the desired number of results is reached:

   return (object.objectSchema.name == "SMALL" || object.objectType.name == "Employees") && object.label.contains("John") && (connectedTicketsIndex.contains(object.id) || object.inboundReferences.isNotEmpty())

Tips to optimize AQL queries

Limit the scope of your query

Limit the scope of your query by including basic clauses (such as object type or ID) to rule out many objects at once and load results faster.

Example 1
To find all employees called “John”, you can execute the following query:

Label LIKE "John"

The above query tests against a million objects (from the object schema LARGE). However, to save significant processing time, we can use the below query (though the AQL is longer) as we know that we’re looking for Employees and test against fewer objects (about 10,000 possible matches):

objectType = Employees AND Label LIKE "John"

Note that the order of the clauses doesn’t matter. You can also use the below query:

Label LIKE "John" AND objectType = Employees

Example 2
Instead of using the following query, which is scoped to the object schema SMALL (and tests against 50 objects):

objectSchema = SMALL AND Name == "Sydney Office"

You can provide object types or list of object keys to narrow the scope (to test against about 10 objects):

objectSchema = SMALL AND objectType = Office AND Name == "Sydney Office"

The predicate for the above query looks similar to below (and the match for the name is tested against 10 objects):

return object.objectSchema = "SMALL" && object.objectType == "Office" && object.name == "Sydney Office"

Example 3
AQL tests that contain the inboundReferences(), outboundReferences(), and connectedTickets() functions are computationally intensive when they contain AQL or JQL parameters. 

The predicate of the following query is simple and if you’re running this query in the AQL search or the object schema page, an object schema is selected by default.

object NOT HAVING connectedTickets() OR object NOT HAVING outboundReferences()

Combine the connectedTickets() function with other AQL clauses using an AND clause

The connectedTickets() function can be executed in 2 ways:

• without JQL: object HAVING/NOT HAVING connectedTickets()
• with JQL: object HAVING/NOT HAVING connectedTickets(JQL)

Assets indexes all of the Assets custom field assignments – every link between a Jira issue and an Assets object is stored in memory. What isn’t stored in memory are the details of the Assets objects or Jira issues in particular. So in the above two methods, getting all connected tickets in the instance is fast, but getting all connected tickets that match a JQL is slow due to the many-to-many relationship between Assets objects and Jira issues.

To use the connected tickets function with a JQL query (for example, object HAVING connectedTickets(status = "In progress")), limit the results using other AQL queries as much as possible.

When the connectedTickets() query is combined with other AQL clauses using the AND operator, Jira Service Management resolves the rest of the AQL, and then runs the JQL search against the connected tickets of the resolved objects.

Example 1
The following query:

objectType = Employee 
  AND Label = John 
  AND Created > startOfYear()
  AND object HAVING connectedTickets(status = "In progress")

will firstly resolve to the below AQL query, which returns a list of object IDs [11,21,31,41] that map to Jira issue IDs [1001,1002,1003].

objectType = Employee
  AND Label = John
  AND Created > startOfYear()

Next, the JQL query ISSUE IN (1001,1002,1003) AND status = "In progress" is executed, which returns the Jira issue IDs [1001,1002] that map to Assets objects [11,21]. The testing phase of this AQL query will be scoped to the Employees object type before the check for objects that have the IDs 11 or 21.

Example 2

Instead of using the following JQL query, which runs against your entire instance:

objectType = Employee 
AND 
(label = John OR object HAVING connectedTickets(user = currentUser()))

use this query so that the JQL query (status = In progress) is run only against the tickets connected to the object IDs in objectType = Employee:

(objectType = Employee AND label = John) 
OR 
(objectType = Employee AND object HAVING connectedTickets(user = currentUser()))

The testing phase in the above query scopes to objectType = Employee only.

Example 3
Queries containing AQL clauses that are nested deeper than the connectedTickets() clause will also be resolved before the connectedTickets() function.

(Name LIKE John OR Name LIKE Jane) 
AND (object NOT HAVING connectedTickets() 
  OR ( (object HAVING inR(object = Device) OR object NOT HAVING outR(object = Host)) 
    AND Name LIKE Jim 
    AND object HAVING connectedTickets(user = currentUser())))

The above AQL will be resolved in the following order:

1. The following AQL gets resolved:

   (object HAVING inR(object = Device) OR object NOT HAVING outR(object = Host)) AND Name LIKE Jim

   inboundReferences() and outboundReferences() AQL queries will resolve themselves:

   object = Device

   object = Host

   If they’re resolved to object IDs [1, 2, 3, 4, 5] and [2, 3, 4, 5, 6] respectively, the query is effectively

   (Id IN (1,2,3,4,5) OR Id IN [2,3,4,5,6]) AND Name LIKE Jim

   This query is then resolved as an AQL query, let’s say to the list of object IDs [2, 3, 4].
   
   

2. The user = currentUser() connected tickets clause can now be resolved.
   

   Similar to Example 1, we get the connected tickets of the remaining object IDs from the index (let’s say Jira issue IDs [2001,2002,2003])

   Next, the JQL, issue in (2001,2002,2003) AND user = currentUser() returns [2001,2002].

   And the issue IDs 2001,2002 map back to object IDs [2,3], that forms the search results.

3. The rest of the AQL is processed as usual. At this point, the AQL is equivalent to:

   (Name LIKE John OR Name LIKE Jane) AND (object NOT HAVING connectedTickets() OR Id in (2,3))

   and the clause object NOT HAVING connectedTickets() is performant as JQL is not required.

In the above example, as the connectedTickets(user = currentUser()) was bundled with other queries such as Name LIKE Jim and the inR() and outR() queries, only a subset of the connected tickets are checked. As many Jira issues are not retrieved, this provides a roughly linear performance increase and also consumes less memory.