last_name
, experience
, and job_title
). See Request Information why data.
precedes each Request variable in each example.LOWER
Function to Disregard Case Sensitivity in Strings and Request Variableslower
function to disregard case-sensitivity evaluation by lower-casing strings and Request variable names. The lower
function is useful for when you are unsure of the case sensitivity of the comparing string or Request variable name.lower(RequestVariable)
where RequestVariable
is the Request variable namelower(data.job_title) LIKE "prod%" OR lower(data.job_title) LIKE "proj%"
AND
, OR
, and LIKE
are not case-sensitive. PMQL operators are capitalized in this document for easier readability.data.last_name = "Canera"
=
data.last_name = "Canera"
!=
data.last_name != "Canera"
<
data.experience < 2
>
data.experience > 2
<=
data.experience <= 2
>=
data.experience >= 2
AND
data.last_name = "Canera" AND data.experience > 2
OR
data.experience <= 2 OR data.experience >= 5
(data.job_title = "product manager" OR data.job_title = "project manager") AND data.experience > 5
LIKE
Operator for Wildcard Pattern MatchingLIKE
operator, then include wildcards %
or _
within the quotation marks ("
) of your search parameter.LIKE
operator is not case sensitive. However, this operator is capitalized in this document for easier readability.%
wildcard represents zero, one, or more characters. The _
wildcard represents exactly one character.request LIKE "P%"
finds Requests associated with all Processes that begin with P
.status LIKE "c%"
finds Requests with both Completed and Canceled statuses.data.last_name LIKE "Ca%"
finds all values from Requests that begin with Ca
in the last_name
Request variable.data.last_name LIKE "Ca___"
finds all values from Requests that begin with Ca
and those that match three following characters in the last_name
Request variable.task LIKE "T%"
finds all Tasks that begin with T
.LIKE
operator:completed
property for the Task
data type that includes an example how to find data in JSON arrays​LIKE
operator with the %
wildcard to find text in a specified JSON array within Request data. Consider the following JSON array in Request data that contains two JSON objects. Each JSON object contains the first name, last name, and email address.company
. PMQL finds the string company
regardless of what string precedes or follows the sought pattern because the %
wildcard disregards all content in the JSON array preceding and following that pattern.IN
and NOT IN
Operators for Array ValuesIN
operator to search for data where the value of the specified property is one of multiple specified values. NOT IN
operator to search for data where the value of the specified property is not one of multiple specified values.2021-07-01
or 2021-05-01
:
(status IN ["Completed", "Error"]) AND data.date NOT IN ["2021-07-01", "2021-05-01"]
2021-07-01
or 2021-05-01
:
(status IN ["Completed", "Error"]) AND data.date IN ["2021-07-01", "2021-05-01"]
admin
or Melissa
. The last modified is equal or major to 2020-07-01 00:00:00
. participant IN ["admin", "Melissa"] AND status NOT IN ["Completed", "In Progress"] AND modified >= "2020-07-01 00:00:00"
CAST
Function to Convert Data TypesCAST
function is a standard SQL syntax that PMQL supports for specific data types.CAST
function is not case sensitive. However, this function is capitalized in this document for easier readability.CAST
function converts data from one data type to another. PMQL supports the CAST
function for the following data types:2
), convert that value to text ("2").2
). For example, use the CAST
function in a PMQL query to perform a numerical comparison to the Experience
Request variable that stores a job candidate's experience in an industry to find job candidates with two (2) years of experience or greater, use the following PMQL query: CAST(data.Experience as number) >= 2
.NOW
Keyword to Perform Temporal Comparisons from NowNOW
keyword is not case sensitive. However, this keyword is capitalized in this document for easier readability.NOW
keyword represents the current datetime. Use the NOW
keyword in PMQL search queries to find Requests or Tasks in the following ways:second
minute
hour
day
date
operator
+
or -number
interval
date
represents the dateoperator
represents the comparative operator+
or -
represents the addition or subtraction (respectively) from the date
number
represents the number to add or subtract from the date
interval
is the interval of timeProcessName
that are not more than two (2) days old:ProcessName
in which its Request participants are 25 years old or younger by only having their date of birth in a Request variable called DOB
, use the following PQML search parameter: