Filtering List APIs

Filtering results from list APIs can be achieved using the filter_expression parameter.

The filter_expression parameter is currently only available on the list APIs that DO NOT end in _query. Although the syntax is very similar to filtering _query APIs, the filter_expression parameter has additional functions and methods that are not available in the _query APIs.

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

For example:

https://my_workspace.api.vitruvi.cc/api/v1/reporting/crews_export/?filter_expression=crew_id eq 23

will filter the results so that only the crew with id 23 is returned.

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

https://my_workspace.api.vitruvi.cc/api/v1/reporting/invoices_export?limit=1000&offset=0&filter_expression=invoice_status in ('pending','invoiced') and (invoice_modified lt '2021-02-01T00:00:00-03:00' or invoice_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.

Filter Expression 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)”
in range	<<field_expression>> in range (<<start_literal>>, <<end_literal>>)	Values in a specified range of potential values. Needs to be followed by a start and end for the range. Example, “id in range (5, 75)” or “modified_date in range ('2023-01-01', '2023-06-01')”

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	Description	Example
startswith(<<field_expression>>, <<literal>>)	Case-sensitive starts-with.	startswith(payee_name, ‘M’)
istartswith(<<field_expression>>, <<literal>>)	Case-insensitive starts-with.	istartswith(payee_name, ‘M’)
endswith(<<field_expression>>, <<literal>>)	Case-sensitive ends-with.	endswith(payee_name, ‘town’)
iendswith(<<field_expression>>, <<literal>>)	Case-insensitive ends-with.	iendswith(payee_name, ‘town’)
contains(<<field_expression>>, <<literal>>)	Case-sensitive containment test.	contains(payee_name, 'ohn')
icontains(<<field_expression>>, <<literal>>)	Case-insensitive containment test.	icontains(payee_name, 'ohn')
exact(<<field_expression>>, <<literal>>)	Exact match (i.e. equals).	exact(payee_name, 'Microsoft Inc.')
iexact(<<field_expression>>, <<literal>>)	Case-insensitive exact match.	iexact(payee_name, 'Microsoft Inc.')
regex(<<field_expression>>, <<literal>>)	Case-sensitive regular expression match. The regular expression syntax is that of PostgreSQL.	regex(payeename, 'denver\d{1,3}')
iregex(<<field_expression>>, <<literal>>)	Case-insensitive regular expression match. The regular expression syntax is that of PostgreSQL.	iregex(payeename, 'denver\d{1,3}')
isempty(<<field_expression>>)	Is empty (blank or null).	isempty(payee_city)

Data functions:

Function	Description	Example
tolower(<<field_expression>>)	Convert string to lower case.	tolower(payee_name) eq ‘microsoft’
toupper(<<field_expression>>)	Convert string to upper case.	toupper(payee_name) eq ‘MICROSOFT’
date(<<field_expression>>)	For datetime fields, casts the value as date.	date(modified) eq ‘2023-01-01’
year(<<field_expression>>)	For date and datetime fields, extracts the year.	year(modified) eq 2023
month(<<field_expression>>)	For date and datetime fields, extracts the month.	month(modified) eq 12
day(<<field_expression>>)	For date and datetime fields, extracts the day.	day(modified) eq 31
quarter(<<field_expression>>)	For date and datetime fields, extracts the quarter.	quarter(modified) eq 4
week(<<field_expression>>)	For date and datetime fields, extracts the week.	week(modified) eq 52
week_day(<<field_expression>>)	For date and datetime fields, a ‘day of the week’ match. Takes an integer value representing the day of week from 1 (Sunday) to 7 (Saturday).	week_day(modified) eq 2
time(<<field_expression>>)	For datetime fields, casts the value as time.	time(modified) gt ‘14:30’
hour(<<field_expression>>)	For date and datetime fields, extracts the hour.	hour(payee_name) eq 22
minute(<<field_expression>>)	For date and datetime fields, extracts the minute.	minute(payee_name) eq 59
second(<<field_expression>>)	For date and datetime fields, extracts the second.	second(payee_name) eq 33

Constants:
- null: represent a Null value.

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

filte_expressionr=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_expression=payee_data/city eq 'New York'