PMQL Syntax to Search ProcessMaker Data

Overview
ProcessMaker Query Language (PMQL) builds upon standard search query language (SQL) syntax to use properties unique to ProcessMaker. PMQL supports the following SQL syntax:
​Operators and grouping multiple criteria​
​CAST function to convert data types​
​NOW keyword to perform temporal comparisons from now​
Operators and Grouping Multiple Criteria
PMQL supports the following operators in and between each search criterion. The example for each comparative or logical operator assumes that the PMQL query searches against Request data for a Request variable (such as 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 variables​
​Comparative and logical operators​
​LIKE operator for wildcard pattern matching​
​IN and NOT IN operators for array values​
LOWER Function to Disregard Case Sensitivity in Strings and Request Variables
PMQL queries are case sensitive in regard to comparative and logical operators. If querying for a string or the value of a Request variable name, PMQL returns results only if the case exactly matches your query. Use the lower 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.
Usage
lower(RequestVariable) where RequestVariable is the Request variable name
Example
lower(data.job_title) LIKE "prod%" OR lower(data.job_title) LIKE "proj%"
Comparative and Logical Operators
PMQL operators such as AND, OR, and LIKE are not case-sensitive. PMQL operators are capitalized in this document for easier readability.
Spaces are allowed between operators. Example: data.last_name = "Canera"
Operator Description
Syntax
Example
Equal to
=
data.last_name = "Canera"
Not equal to
!=
data.last_name != "Canera"
Less than
<
data.experience < 2
Greater than
>
data.experience > 2
Less than or equal to
<=
data.experience <= 2
Greater than or equal to
>=
data.experience >= 2
Search multiple required properties (logical operator)
AND
data.last_name = "Canera" AND data.experience > 2
Search for any of multiple properties (logical operator)
OR
data.experience <= 2 OR data.experience >= 5
Group multiple logical operators
n/a
(data.job_title = "product manager" OR data.job_title = "project manager") AND data.experience > 5
Pattern matching
LIKE
See LIKE Operator for Wildcard Pattern Matching.
LIKE Operator for Wildcard Pattern Matching
Use the LIKE operator, then include wildcards % or _ within the quotation marks (") of your search parameter.
The LIKE operator is not case sensitive. However, this operator is capitalized in this document for easier readability.
The % wildcard represents zero, one, or more characters. The _ wildcard represents exactly one character.
Wildcard Usage Examples
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.
See the following properties for more examples how to use the LIKE operator:
​requester property for the Request data type
​completed property for the Task data type that includes an example how to find data in JSON arrays​
​modified property for the Task data type
​task property for the Task data type
​created property for the Collection data type
Find Data in JSON Arrays
Use the 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.
1
"Personal": [
2
  {
3
    "FirstName": "Louis",
4
    "LastName": "Canera",
5
    "Email": "[email protected]"
6
  },
7
  {
8
    "FirstName": "Jane",
9
    "LastName": "Lowell",
10
    "Email": "[email protected]"
11
  }
12
]
Copied!
Use the following PMQL search query to find both persons in Request data based on the string 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.
1
data.Personal LIKE "%company%"
Copied!
See Example 2 for the completed property for the Task data type for another example.
IN and NOT IN Operators for Array Values
Use the IN operator to search for data where the value of the specified property is one of multiple specified values. 
Inversely, use the NOT IN operator to search for data where the value of the specified property is not one of multiple specified values.
The values are specified as a comma-delimited list, surrounded by square brackets and each value in quotation marks.
See the following examples:
Find for completed or erroneous Tasks where the Request Date is not 2021-07-01 or 2021-05-01:
(status IN ["Completed", "Error"]) AND data.date NOT IN ["2021-07-01", "2021-05-01"]
Find for completed or erroneous Requests where the Request Date is 2021-07-01 or 2021-05-01:
(status IN ["Completed", "Error"]) AND data.date IN ["2021-07-01", "2021-05-01"]
Find for completed or in progress Requests where the participant is 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 Types
The CAST function is a standard SQL syntax that PMQL supports for specific data types.
The CAST function is not case sensitive. However, this function is capitalized in this document for easier readability.
The CAST function converts data from one data type to another. PMQL supports the CAST function for the following data types:
Text: If the Request data stores the Request variable in the PMQL query as an integer (such as 2), convert that value to text ("2").
Number: If the Request data stores the Request variable in the PMQL query as text (such as "2"), convert that value to a number (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.
See the following properties for examples:
​modified property for the Request data type
​modified property for the Collection data type
NOW Keyword to Perform Temporal Comparisons from Now
The NOW keyword is not case sensitive. However, this keyword is capitalized in this document for easier readability.
The NOW keyword represents the current datetime. Use the NOW keyword in PMQL search queries to find Requests or Tasks in the following ways:
Dynamically compare the age of a Request or Task from the current datetime based on when the sought Request or Task was created or last modified. PMQL supports the following intervals of time:
second
minute
hour
day
Perform arithmetic operations on dates by using the following syntax:
date operator + or -number interval
where:
date represents the date
operator 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 time
See Example to Find Request Last Modified. See also the following property examples:
​participant property for the Request data type
​request property for the Request data type
​completed property for the Task data type
​created property for the Task data type
​due property for the Task data type
​element_id property for the Task data type
​modified property for the Task data type
​request property for the Task data type
​started property for the Task data type
​status property for the Task data type
​modified property for the Collection data type
Dynamically determine a chronological value of a Request variable from Request data as part of the PMQL search query. See Example Using Request Data.
Example to Find Request Last Modified
Use the following PMQL query search query to find Requests for ProcessName that are not more than two (2) days old:
1
(modified < NOW -2 day) AND (request = "ProcessName")
Copied!
Example Using Request Data
Find Requests from 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:
1
(data.DOB > NOW -9125 day) AND (request = "ProcessName")
Copied!
Calculate the date of birth by subtracting 9125 days from the current datetime (365 * 25 = 9125).
Related Topics
What is PMQL?
PMQL Data Types: Request, Task, and Collection
Request Data Type PMQL Properties
Task Data Type PMQL Properties
Collection Data Type PMQL Properties
What is Request Data?
What is a Request Variable?