PMQL Syntax to Search ProcessMaker Platform Data

Understand the standard SQL syntax from which to search ProcessMaker Platform 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 Platform. 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.

"Personal": [
  {
    "FirstName": "Louis",
    "LastName": "Canera",
    "Email": "[e-mail]"
  },
  {
    "FirstName": "Jane",
    "LastName": "Lowell",
    "Email": "[e-mail]"
  }
]

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:

• 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:

(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).

Related Topics

pageWhat is PMQL?pagePMQL Data Types: Request, Task, and CollectionpageRequest Data Type PMQL PropertiespageTask Data Type PMQL PropertiespageCollection Data Type PMQL PropertiespageSpecial Characters in API Call PMQL SearchespageWhat is Request Data?pageWhat is a Request Variable?