9.2 Advanced JQL Functions & Dynamic Date Searches

9.2 Advanced JQL Functions & Dynamic Date Searches

Quick Summary: Writing maintainable JQL requires shifting from hardcoded, brittle values to dynamic functions that evaluate at query execution time. Jira Cloud provides built-in functions for user context (currentUser()), group membership (membersOf()), and dynamic calendar-boundary date math (startOfDay(), endOfWeek(), startOfMonth()). Furthermore, administrators must understand the underlying mechanics of Lucene text indexing—including stemming, wildcards, and reserved stop words—as well as Jira Cloud's modern, unified parent field for querying across multi-tier issue hierarchies.

---

Dynamic User & Group Functions

Hardcoding specific user names or account IDs into JQL filters creates high administrative maintenance. When team members change roles or depart the organization, every filter, board, and dashboard gadget referencing them breaks or becomes obsolete.

1. currentUser()

The currentUser() function dynamically evaluates to the Atlassian account ID of the authenticated user currently executing the query, viewing the board, or loading the dashboard gadget.

• Universal Queue Pattern: Creating a saved filter defined as resolution IS EMPTY AND assignee = currentUser() allows an entire department of 500 engineers to share a single dashboard gadget ("My Open Work"). When User A views the gadget, it displays User A's tasks; when User B views it, it displays User B's tasks.
• Audit & Activity Pattern: updatedBy = currentUser() or status CHANGED BY currentUser().

2. membersOf("group-name")

The membersOf() function evaluates whether a user field contains a user who belongs to a designated Jira user group.

• Syntax: <userField> IN membersOf("group-name")
• Example Queries:

  /* Find all unresolved defects assigned to anyone in the frontend engineering group */
  issuetype = Bug AND resolution IS EMPTY AND assignee IN membersOf("frontend-devs")
  
  /* Find all security tickets reported by contractors */
  project = SEC AND reporter IN membersOf("external-contractors")
  

[!NOTE] The membersOf() function evaluates Jira groups (and Atlassian teams by team ID), not project roles. There's no function that returns the members of a project role. The related function spacesWhereUserHasRole() (formerly projectsWhereUserHasRole()) returns the projects in which the current user holds a given role, for example project in spacesWhereUserHasRole("Developers"). If membersOf() would return more than 10,000 users, the search won't run.

---

Date Functions & Dynamic Date Math

Jira Cloud supports both absolute date filtering (e.g., created >= "2026-01-01") and dynamic date math. For ongoing reporting and dashboard gadgets, hardcoded dates are unacceptable because they require continuous manual updates.

Jira Cloud Calendar Boundary Functions

Rolling Window vs. Calendar-Boundary Anchoring

A critical distinction tested on the ACP-120 exam is the difference between relative rolling durations and calendar-boundary date functions.

CURRENT TIME: Wednesday, June 17, 2026 at 15:30:00

[1] ROLLING DURATION QUERY: created >= -7d
Evaluates: Exact 168 hours prior to current millisecond.
Window: Wednesday, June 10, 2026 at 15:30:00 --> June 17, 2026 at 15:30:00
Issues created on June 10 at 09:00 AM are EXCLUDED.

[2] CALENDAR-BOUNDARY QUERY: created >= startOfDay("-7d")
Evaluates: 00:00:00 (Midnight) exactly 7 calendar days ago.
Window: Wednesday, June 10, 2026 at 00:00:00 --> Present
Issues created on June 10 at 09:00 AM are INCLUDED.

For enterprise management reporting (e.g., "All tickets logged in the past week"), rolling windows create confusion because tickets logged earlier in the day roll out of the search window mid-afternoon. Administrators should almost always choose startOfDay("-7d") or startOfWeek() for reporting consistency.

---

Text Searching: Lucene Mechanics, Stemming & Reserved Words

Jira Cloud indexes free-text fields (summary, description, environment, and user comments) for searching with the ~ (CONTAINS) operator.

1. Word Stemming

The search engine applies linguistic stemming when indexing and searching English text. Stemming reduces a word to its root morphological base form:

• Searching summary ~ "test" will match issues containing test, tests, testing, and tested.
• Searching description ~ "connect" will match connection, connected, connecting, and connects.

2. Reserved Stop Words

To keep text search efficient, Jira ignores common English words known as stop words. These words are completely omitted from the search index:

a, and, are, as, at, be, but, by, for, if, in, into, is, it, no, not, of, on, or, such, that, the, their, then, there, these, they, this, to, was, will, with

If a user searches for an exact phrase containing reserved words using unescaped syntax:

summary ~ "error in login"   /* 'in' is ignored; Jira searches for 'error' AND 'login' */

This query will return issues containing "error during login", "login error", and "error when login failed".

3. Exact Phrase Searching with Escaped Quotes

When a developer or support engineer needs to find an exact, literal error string (e.g., from a stack trace or log file), they must enclose the phrase in escaped double quotes:

summary ~ "\"error in login\""

The escaped quotes ask Jira to match the words together, in that order, instead of anywhere in the field.

4. Wildcard Characters and Restrictions

Jira supports two wildcard characters in text queries:

• ?: Matches exactly one single character (e.g., te?t matches test or text).
• *: Matches zero, one, or multiple characters (e.g., micro* matches microservice, microsoft, microchip).

[!WARNING] Leading Wildcards are Prohibited: In Jira Cloud text searches, a wildcard character (* or ?) cannot be used as the first character of a search term. Querying summary ~ "*service" will immediately trigger a JQL validation error because leading wildcards aren't supported in Jira Cloud text search.