Query Functions¶

Jira-select provides a long list of functions out-of-the-box, and you can add your own if these are not enough.

Jira¶

get_issue(ticket_number: str) → jira.resources.Issue¶

Fetch a Jira issue by its issue key (e.g. MYPROJECT-1045).

This will return a jira.resources.Issue object; you can access most fields via its fields property, eg:

get_issue(field_holding_issue_key).fields.summary

get_issue_snapshot_on_date(issue: jira.resources.Issue) → jira_select.types.IssueSnapshot:¶

Reconstruct the state of an issue at a particular point in time using the issue’s changelog.

You will want to pass the literal value issue as the first parameter of this function. Jira-select provides the jira.resources.Issue object itself under that name, and this function will use both that object and the changes recorded in the changelog field for getting an understanding of what the issue looked liked at a particular point in time.

This function requires that you set the query expand option such that it includes changelog for this to work correctly – if you do not do that, this function will fail.

select:
  snapshot: get_issue_snapshot_on_date(issue, parse_datetime('2022-01-01'))
from: issues
expand:
- changelog

The returned snapshot is not a jira.resources.Issue object, but instead a jira_select.types.IssueSnapshot object due to limitations around what kinds of data can be gathered from the snapshot information. The most important difference between a jira_select.types.IssueSnapshot and a jira.resources.Issue object is that the jira_select.types.IssueSnapshot object is a simple dict[str,str] object in which the value of the dict entries is always the str-ified field value.

sprint_name(sprint_blob: str) → Optional[str]¶: Shortcut for returning the name of a sprint via its ID. Equivalent to calling sprint_details(sprint_blob).name.

sprint_details(sprint_blob: str) → Optional[jira_select.functions.sprint_details.SprintInfo]¶

Returns an object representing the passed-in sprint blob.

Jira returns sprint information on an issue via strings looking something like:

com.atlassian.greenhopper.service.sprint.Sprint@14b1c359[id=436,rapidViewId=153,state=CLOSED,name=MySprint,goal=Beep Boop,startDate=2020-03-09T21:53:07.264Z,endDate=2020-03-23T20:53:00.000Z,completeDate=2020-03-23T21:08:29.391Z,sequence=436]

This function will extract the information found in the above string into a jira_select.functions.sprint_details.SprintInfo object allowing you to access each of the following properties:

id: Sprint ID number
state: Sprint state
name: Sprint name
startDate: Sprint starting date (as datetime)
endDate: Sprint ending date (as datetime)
completeDate: Sprint completion date (as datetime)

get_sprint_by_id(id: int) → Optional[jira.resources.Sprint]¶: This function will request the information for the sprint specified by id from your Jira server and return it as a jira.resources.Sprint object.

get_sprint_by_name(board_name_or_id: Union[str, int], sprint_name: str) → Optional[jira.resources.Sprint]¶: This function will request the information for the sprint matching the specified name and belonging to the specified board. This will be returned as a jira.resources.Sprint resource.

field_by_name(row: Any, display_name: str) → Optional[str]¶

Returns value for field having the specified display name.

Note

You probably do not nee to use this function. We provide another, simpler, method for getting the value of a field by its human-readable name– just place the human-readable name in between curly braces in your query expression; eg:

select
  Story Points: "{Story Points}"
from: issues

Note

You will almost certainly want to provide the parameter named issue as the first argument to this function. Jira-select provides the raw row data to functions under this variable name.

In Jira, custom fields are usually named something like customfield_10024 which is, for most people, somewhat difficult to remember. You can use this function to get the field value for a field by its display name instead of its ID.

Example:

select
  - field_by_name(issue, "Story Points") as "Story Points"
from: issues

estimate_to_days(estimate_string: str, day_hour_count=8) → Optional[float]¶

Converts a string estimation (typically stored in timetracking.originalEstimate) into an integer count of days.

The timetracking.originalEstimate field contains values like 1d 2h 3m; using this function will transform such a value into 1.25625.

flatten_changelog(changelog) → List[jira_select.functions.flatten_changelog.ChangelogEntry]¶

Converts changelog structure from your returned Jira issue into a flattened list of jira_select.functions.flatten_changelog.ChangelogEntry instances.

Note

You must use the expand option of changelog for Jira to return to you changelog information in your query; eg:

select:
  changelog: flatten_changelog(changelog)
from: issues
expand:
- changelog

If you do not provide the necessary expand option, this function will raise an error.

Every member of the returned list has the following properties:

author (str): Author of the change
created (datetime.datetime): When the change took place
id (int): The ID of the changelog entry
field (str): The name of the field that was changed
fieldtype (str): The type of the field that was changed
fromValue (Optional[Any]): The original value of the field. Note that the original Jira field name for this is from.
fromString (Optional[str]): The original value of the field as a string.
toValue (Optional[Any]): The final value of the field. Note that the original Jira field name for this is ``to`.
toString (Optional[str]): The final value of the field as a string.

This returned list of records can be filtered with simple_filter to either find particular entries or filter out rows that do not have an entry having particular characteristics.

get_linked_issue_keys(issue: jira.resources.Issue, link_type: str | None = None) → list[str]:¶

Return a list of issue keys that are related to the relevant issue via the specified relation type (e.g. causes, is associated with, etc.).

You will want to pass the literal value issuelinks as the first parameter of this function. This will provide this function with the list of issuelinks your issue has.

If link_type is unspecified, all related issue keys will be returned.

For example, to find the keys for all issues that were caused by a particular issue, you could run the following query:

select:
  caused_bugs: get_linked_issue_keys(issuelinks, 'causes')
from: issues
where:
- type = 'Bug'

Subquery¶

subquery(subquery_name, **params) → Any:¶

Runs a subquery by name with the provided parameters.

For example: you can get the time intervals during which an issue and its associated subtasks were in the “In Progress” status with a query like so:

select:
  self_and_child_intervals_in_progress: interval_matching(issue, status="In Progress") | union(subquery("children", key=issue.key))
from: issues
subqueries:
   children:
      select:
        in_progress_intervals: interval_matching(issue, status='In Progress')
      from: issues
      where:
      - parent = "{params.key}"
      expand:
      - changelog
expand:
- changelog

Your specified **params will become available to the subquery via {params.*}; in the above example, {params.key} will be set to the value of the outer query’s issue.key.

Unless specifically specified, a subquery will use the same cache settings as the parent query.

Warning

If you would like your subquery’s cache to be effective, only pass simple values in **params.

The string representation of an object is used for calculating cache keys, and many objects include information in their default string representations that vary between instantiations. If things like, for example, the memory address of an object appears in its string representation, the cache key will never match, and the cached value will not be used.

A common way that this problem might occur is if you were to pass the entire issue object to the subquery. Instead of passing the entire issue object to the subquery, pass simple values from it as shown in the example above.

Time Analysis¶

interval_matching(issue, **query_params: dict[str, Any]) → portion.Interval¶

See simple_filter function for information about how to specify query_params.

Will return an interval covering segments in which the provided issue matches the conditions specified by query_params.

Note

Contrary to what you might guess, a single portion.Interval object can represnt multiple ranges of time.

Note that portion.Interval objects can be used with logical operations like |, &, and -.

interval_size(interval: portion.Interval) → datetime.timedelta¶: For a provided interval, return the total amount of time that the interval’s segments span.

interval_business_hours(min_date: datetime.date | None = None, max_date: datetime.date | None = None, start_hour: int = 9, end_hour: int = 17, timezone_name: str | None = None, work_days: Iterable[int] = (1, 2, 3, 4, 5)) → portion.Interval:¶

Returns an interval having segments that correspond with the “business hours” specified by your paramters.

This is particularly useful when used in conjunction with interval_matching and interval_size above for determining the amount of time an issue was actively in a particular state, for example:

select:
  total_time_in_progress: interval_size(interval_matching(issue, status="In Progress") & interval_business_hours(parse_date(created)))
from: issues

This will find all segments of time during which the relevant issue was in the “In Progress” status during business hours, then return the amount of time that those segments spanned.

Note

A naive implementation of this sort of time analysis might use actual, raw clock time, but consider the following two situations:

MYPROJECT-01 moves from “To Do” into “In Progress” at 4:55PM, just five minutes before the end of the day, then the next day moves from “In Progress” into “Done” at 9:05AM, five minutes after the beginning of the next day.
MYPROJECT-02 moves from “To Do” into “In Progress” at 10:00AM and in the same day from “In Progress” into “Done” at 4:00PM.

Clearly, MYPROJECT-02 was being “worked on” for more time than MYPROJECT-01, but let’s see how various algorithms might measure that time.

If we use raw clock time:

MYPROJECT-01: 16.2h (81 times more than the actual working time)
MYPROJECT-02: 6h

If we only measure time happening between 9A and 5P:

MYPROJECT-01: 0.2h (the actual working time)
MYPROJECT-02: 6h (the actual working time)

Of course, this does introduce one inaccuracy that may, depending on how predicable your team’s working hours are, make this behavior undesirable: time spent working on an issue outside of business hours isn’t counted. Typically, though, the amount of time an issue might be worked on outside those hours will be much smaller than the amount of excess time using raw clock time directly would count.

min_date: The minimum date to add the business hours of to your interval. By default, 365 days before now.
max_date: The (exclusive) maximum date to add the business hours of to your interval. By default: tomorrow.
start_hour: The work day starting hour. Defaults to 9 (i.e. 9 AM)
end_hour: The work day ending hour. Defaults to 17 (i.e 5 PM)
timezone_name: The timezone to interpret start_hour and end_hour in.
work_days: The days of the week to count as work days; 0 = Sunday,
1 = Monday… 6 = Saturday.

Data Traversal¶

extract(field: Iterable[Any], dotpath: str) → Iterable[Any]¶

For every member of field, walk through dictionary keys or object attributes described by dotpath and return all non-null results as an array.

Note

Although this will work, it is not necessary to use this for traversing into properties of grouped rows. If your selected field is an object having a value you’d like to select, you can simply use dotpath traversal to reach the value you’d like.

This function works for both dictionaries and objects.

flatten_list(field: List[List[Any]]) → List[Any]¶

For a list containing a lists of items, create a single list of items from the internal lists.

The above is a little bit difficult to read, but in principle what this function does is convert values like:

[[1, 2, 3], [4, 5, 6]]

into a single list of the shape:

[1, 2, 3, 4, 5, 6]

Dates¶

now(**replacements) → datetime.datetime¶

Return “now” as a timezone-aware datetime.datetime object.

Additional parameters can be passed via keyword arguments; these values will be applied to the datetime.datetime object via its replace method. See Python’s documentation for for more information .

If you would like to obtain a timezone-unaware datetime object, pass tzinfo=None as a keyword argument.

timedelta(days=0, seconds=0, microseconds=0, milliseconds=0, minutes=0, hours=0, weeks=0) → datetime.timedelta¶

Returns a datetime.timedelta object.

This object can be used in math with existing ``datetime.datetime``objects.

datetime(year, month, day, hour=0, minute=0, second=0, microsecond=0, tzinfo=None, *, fold=0) → datetime.datetime¶

Returns a datetime.datetime object.

To obtain a date object, call .date() on the return value of this function.

parse_datetime(datetime_string: str, *args, **kwargs) → datetime.datetime¶

Parse a date string into a datetime object.

This relies on python-dateutil; there are many additional options available that you can find documented here.

parse_date(date_string: str, *args, **kwargs) → datetime.date¶

Parse a date string into a date object.

This relies on python-dateutil; there are many additional options available that you can find documented here.

Intervals¶

empty_interval() → portion.Interval¶

closed_interval() → portion.Interval¶

open_interval() → portion.Interval¶

openclosed_interval() → portion.Interval¶

closedopen_interval() → portion.Interval¶

Json¶

json_loads(json: str) → Union[Dict, List]¶: Parse a JSON string.

json_dumps(obj: Union[Dict, List]) → str¶: Encode a dictionary or list into a JSON string.

Math¶

Basic¶

See more in information in Python’s Documentation.

abs(value: float) → str¶

max(values: List[Any]) → Any¶

min(values: List[Any]) → Any¶

pow(base: float, exponent: float, mod: Optional[int]) → float¶

round(value: float, ndigits: int = 0) → float¶

sum(values: List[Any]) → Any¶

Averages & measures of central location¶

See more in information in Python’s Documentation.

mean(values: List[Any]) → Any¶

fmean(values: List[Any]) → float¶: Requires Python 3.8

geometric_mean(values: List[Any]) → float¶: Requires Python 3.8

harmonic_mean(values: List[Any]) → Any¶

median(values: List[Any]) → Any¶

median_low(values: List[Any]) → Any¶

median_high(values: List[Any]) → Any¶

median_grouped(values: List[Any], interval: int = 1) → Any¶

mode(values: List[Any]) → Any¶

multimode(values: List[Any]) → List[Any]¶: Requires Python 3.8

quantiles(values: List[Any], n=4, method=Literal['exclusive', 'inclusive']) → Iterable[Iterable[Any]]¶: Requires Python 3.8

Measures of spread¶

See more in information in Python’s Documentation.

pstdev(values: List[Any], mu=Optional[float]) → Any¶

pvariance(values: List[Any], mu=Optional[float]) → Any¶

stdev(values: List[Any], xbar=Optional[float]) → Any¶

variance(values: List[Any], xbar=Optional[float]) → Any¶

Numeric Representation¶

See more in information in Python’s Documentation.

bin(value: int) → str¶

hex(value: int) → str¶

oct(value: int) → str¶

ord(value: str) → int¶

List Operations¶

union(iterable: Iterable[X]) → X¶

Types¶

See more in information in Python’s Documentation.

bool(value: Any) → bool¶

int(value: Any) → int¶

set(value: Any) → set¶

str(value: Any) → str¶

tuple(value: Any) → tuple¶

type(value: Any) → str¶

Sorting¶

See more in information in Python’s Documentation.

reversed(iterable: List[Any]) → Iterable[List[Any]]¶

sorted(iterable: List[Any]) → Iterable[List[Any]]¶

Filtering & Mapping¶

simple_filter(iterable: Iterable[Any], **query_params: Dict[str, Any]) → Iterable[Any]¶

simple_filter_any(iterable: Iterable[Any], **query_params: Dict[str, Any]) → Iterable[Any]¶

These functions provide you with a simple way of filtering lists using a syntax reminiscent of Django’s ORM query filter parameters.

simple_filter: All provided query_params must match for the row to be returned.
simple_filter_any: At least one provided param in query_params must match for the row to be returned.

For example; to find issues having become resolved between two dates, you could run a query like the following:

select: "*"
from: issues
filter:
- simple_filter(
      flatten_changelog(changelog),
      field__eq="resolution",
      toValue__ne=None,
      created__lt=parse_datetime("2020-02-02"),
      created__gt=parse_datetime("2020-02-01"),
  )
expand:
- changelog

Consult the documentation for QueryableList for information about the full scope of parameters available.

Python Builtin Functions¶

See more in information in Python’s Documentation.

filter(callable: Callable, Iterable[Any]) → Iterable[Any]¶

map(callable: Callable, Iterable[Any]) → Iterable[Any]¶

Logic Shortcuts¶

See more in information in Python’s Documentation.

all(iterable: List[Any]) → bool¶

any(iterable: List[Any]) → bool¶

Counting¶

See more in information in Python’s Documentation.

len(iterable: List[Any]) → int¶: You might be tempted to use count() given how we share many patterns with SQL, but this is what you actually want to use.

Ranges¶

See more in information in Python’s Documentation.

range(stop: int) → Iterable[int]¶

range(start: int, stop: int) → Iterable[int]¶

range(start: int, stop: int, step: int) → Iterable[int]¶

Random¶

See more in information in Python’s Documentation.

random() → float¶

randrange(stop: int) → int¶

randrange(start: int, stop: int) → int¶

randrange(start: int, stop: int, step: int) → int¶

randint(low: int, high: int) → int¶

choice(Sequence[Any]) → Any¶

Query Functions¶

Jira¶

Subquery¶

Time Analysis¶

Data Traversal¶

Dates¶

Intervals¶

Json¶

Math¶

Basic¶

Averages & measures of central location¶

Measures of spread¶

Numeric Representation¶

List Operations¶

Types¶

Sorting¶

Filtering & Mapping¶

Python Builtin Functions¶

Logic Shortcuts¶

Counting¶

Ranges¶

Random¶

Jira-Select

Navigation

Related Topics