JQL function - lookup

JQL Syntax

lookup(<Table>, <Lookup Column>, <Lookup Value>, <Mapped Column>, <Mode>)

Parameter	Description
<Table>	The lookup table name
<Lookup Column>	The column name to perform the lookup
<Lookup Value>	The value used to perform the lookup
<Mapped Column>	The column name to return if there is any matching row
<Mode>	The mode for returning values first will return the first matching value last will return the last matching value all will return all the matching value

Supported Operators

IN	NOT IN	=	!=	<	>	<=	>=	IS	IS NOT	WAS	WAS IN	WAS NOT	WAS NOT IN	CHANGED

An Example

Using the example from Support multiple categories for Jira Project, if there is a Lookup Table named Project Category Table with 3 columns (Project Name, Project Key, Category)

Project Name	Project Key	Category	Remarks
Customer Service project	CS	BUSINESS	project CS belongs to Business category
Customer Service project	CS	CUSTOMER
Development project	DEV	BUSINESS	project DEV belongs to Business category
Framework Development project	FRAM	CUSTOMER
Framework Development project	FRAM	INTERNAL
QA project	QA	BUSINESS	project QA belongs to Business category
QA project	QA	INTERNAL

The lookup JQL function

CODE

project in lookup("Project Category Table", "Category", "Business", "Project Key", "all")

will be translated to

CODE

project in (CS, DEV, QA)

For reference,

Parameter	Description	Value in Example
<Table>	The lookup table name	Project Category Table
<Lookup Column>	The column name to perform the lookup	Category
<Lookup Value>	The value used to perform the lookup	BUSINESS
<Mapped Column>	The column name to return if there is any matching row	Project Key
<Mode>	The mode for returning values first will return the first matching value last will return the last matching value all will return all the matching values in the table	all

If you use

CODE

project in lookup("Project Category Table", "Category", "Business", "Project Key", "first")

it will be translated to

CODE

project in (CS)

Learn More

Use lookup() function to Support multiple categories for Jira Project
Use lookup() function to Support mapping between Product Owner and Jira Project