PMQL Syntax to Search ProcessMaker Platform Data
Understand the standard SQL syntax from which to search ProcessMaker Platform data using ProcessMaker Query Language (PMQL).
ProcessMaker Query Language (PMQL) builds upon standard search query language (SQL) syntax to use properties unique to ProcessMaker Platform. PMQL supports the following SQL syntax:
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.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.lower(RequestVariable)
where RequestVariable
is the Request variable namelower(data.job_title) LIKE "prod%" OR lower(data.job_title) LIKE "proj%"
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 |
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.request LIKE "P%"
finds Requests associated with all Processes that begin withP
.status LIKE "c%"
finds Requests with both Completed and Canceled statuses.data.last_name LIKE "Ca%"
finds all values from Requests that begin withCa
in thelast_name
Request variable.data.last_name LIKE "Ca___"
finds all values from Requests that begin withCa
and those that match three following characters in thelast_name
Request variable.task LIKE "T%"
finds all Tasks that begin withT
.
See the following properties for more examples how to use the
LIKE
operator:-
completed
property for theTask
data type that includes an example how to 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%"
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
or2021-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
or2021-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
orMelissa
. The last modified is equal or major to2020-07-01 00:00:00
.participant IN ["admin", "Melissa"] AND status NOT IN ["Completed", "In Progress"] AND modified >= "2020-07-01 00:00:00"
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 theCAST
function in a PMQL query to perform a numerical comparison to theExperience
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:
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 dateoperator
represents the comparative operator+
or-
represents the addition or subtraction (respectively) from thedate
number
represents the number to add or subtract from thedate
interval
is the interval of time
- Dynamically determine a chronological value of a Request variable from Request data as part of the PMQL search query. See Example Using Request Data.
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")
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).
Last modified 3mo ago