ProcessMaker Platform Documentation
ProcessMaker 4.4.1
ProcessMaker HomeGive Us FeedbackRelease NotesDownload Open-Source Edition
Search
⌃K
Links

PMQL Syntax to Search ProcessMaker Data

Understand the standard SQL syntax from which to search ProcessMaker data using ProcessMaker Query Language (PMQL).

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:
In conjunction with the syntax in combination with these data types and properties, also use one or more Request variables in one query.

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

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

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:

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.
"Personal": [
{
"FirstName": "Louis",
"LastName": "Canera",
"Email": "[email protected]"
},
{
"FirstName": "Jane",
"LastName": "Lowell",
"Email": "[email protected]"
}
]
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.
data.Personal LIKE "%company%"
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:

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:
  • 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:
(modified < NOW -2 day) AND (request = "ProcessName")

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:
(data.DOB > NOW -9125 day) AND (request = "ProcessName")
Calculate the date of birth by subtracting 9125 days from the current datetime (365 * 25 = 9125).
Previous
What is PMQL?
Next
PMQL Data Types: Request, Task, and Collection
Last modified 9d ago