_Query API Filtering

Filtering results using the query APIs is achieved with the use of the $filter parameter.

📘
As the $filter parameter can become quite long, it is recommended that these APIs are called using the POST method rather than the GET method described here.

As an example:

https://my_workspace.api.vitruvi.cc/api/v1/invoicing/invoices_query?limit=1000&offset=0&$filter=id eq 9

will filter the results so that only the item with id 9 is returned.

Additional operators can be used and filter criteria can be compounded using expressions.

https://my_workspace.api.vitruvi.cc/api/v1/invoicing/invoices_query?limit=1000&offset=0&$filter=status in ('pending','invoiced') and (modified lt '2021-02-01T00:00:00-03:00' or modified gt '2021-04-01T00:00:00-03:00')

will filter the results so that only the items with a status of pending or invoiced AND with a modified date of either before Feb 1, 2021 or after April 1, 2021 will be returned.

_Query API Filtering Operators

The following is the full list of operators supported:

Brackets (order of operations)
Logical operators: and, or, not
List operators:

Operator	Syntax	Description
in	<<field_expression>> in <<list>>	Values in a specified list of potential values. Needs to be followed by a list. Example, “my_text_field in (‘string1’,’string2’)” or “my_number_field in (1,2,3)”

Operator

Syntax

Description

<<field_expression>> in <<list>>

Values in a specified list of potential values. Needs to be followed by a list.

Example, “my_text_field in (‘string1’,’string2’)” or “my_number_field in (1,2,3)”

Comparison operators:

Operator	Syntax	Example
Not equals	<<field_expression>> ne <<literal>>	status ne ‘pending’
Equals	<<field_expression>> eq <<literal>>	status eq ‘pending’
Greater than	<<field_expression>> gt <<literal>>	id gt 50
Less than	<<field_expression>> lt <<literal>>	id lt 50
Greater than or equal	<<field_expression>> gte <<literal>>	id gte 50
Less than or equal	<<field_expression>> lte <<literal>>	id lte 50

Logical functions:

Function	Syntax	Example
String begins with (case sensitive).	startswith(<<field_expression>>, <<literal>>)	startswith(payee_name, ‘M’)
String ends with (case sensitive).	endswith(<<field_expression>>, <<literal>>)	endswith(payee_name, ‘town’)
String contains (case sensitive).	contains(<<field_expression>>, <<literal>>)	contains(payee_name, 'ohn')
Is empty (blank or null).	isempty(<<field_expression>>)	isempty(payee_city)

Data functions:

Function	Syntax	Example
Convert string to lower case (use where operators and logical functions need to be case insensitive).	tolower(<<field_expression>>)	startswith(tolower(payee_name), ‘m’)

Constants:
- null: represent a Null value.

A more complete, but perhaps less useful, example would be:

$filter=status in ('pending','invoiced') and ((modified gt '2021-02-01T00:00:00-03:00' and modified lt '2021-04-01T00:00:00-03:00') or (modified gt '2022-02-01T00:00:00-03:00' and modified lt '2022-12-01T00:00:00-03:00')) and not startswith(tolower(payee_name),'m')

Filtering on sub-items

Some results from the _query APIs return child data sets. For example, payee_data in these results:

{
    "id": 123,
    "payee_name": "Vitruvi Network Solutions",
    "payer_name": "Big Telco",
    "payee_data": {
        "id": 456,
        "address1": "123 Anywhere",
        "city": "New York",
        "region": "",
        "name": "Vitruvi Network Solutions",
        "post_code": "90210",
        "type": "SC"
    },
    "status": "pending",
    "modified": "2022-07-28T16:47:49Z"
}

Filter expressions can also be used on these child data sets by separating the field expressions with a forward slash. Example, $filter=payee_data/city eq 'New York'