Documentation

Raw API Filtering

Suggest Edits

🚧

To be deprecated

Starting in v1.42, there will be a new method for filtering both raw and other list APIs using the new filter_expression paramaeter. This method of filtering raw APIs will still remain and be supported for a while, however, any new projects are encouraged to use the new filter_expression method.

Filtering results using the raw APIs is achieved with additional key-value pairs added to the query parameters. Each key is simply the field that you wish to filter on.

https://my_workspace.api.vitruvi.cc/api/v1/raw/core/customfieldvalue?limit=10000&offset=0&id=283815

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

When no operator is specified, the filter assumes the comparison is equals. However, additional operators can be speecified by appending a double underscore followed by the operator.

https://my_workspace.api.vitruvi.cc/api/v1/raw/core/customfieldvalue?limit=10000&offset=0&modified__gte=2022-05-24

will filter the results so that only the items with a modified date that is greater than, or equal to, 2022-05-24 will be returned.

NOTE: Multiple filter criteria can be used, however, they will be joined with an 'AND'. There is no ability to use an OR.

Raw API Filtering Operators

The following is the full list of operators supported:

OperatorDescriptionExampleSQL Equivalent
__exactExact match (i.e. equals)id__exact=14SELECT ... WHERE id = 14;
__iexactCase-insensitive exact match.name__iexact='beatles'SELECT ... WHERE name ILIKE 'beatles';
__containsCase-sensitive containment test.name__contains='Lennon'SELECT ... WHERE name LIKE '%Lennon%';
__icontainsCase-insensitive containment test.name__icontains='Lennon'SELECT ... WHERE name ILIKE '%Lennon%';
__inIn a given listid__in=1,3,4SELECT ... WHERE id IN (1, 3, 4);
__gtGreater thanid__gt=4SELECT ... WHERE id > 4;
__gteGreater than or equal to
__ltLess than
__lteLess than or equal to
__startswithCase-sensitive starts-with.name__startswith='Lennon'SELECT ... WHERE name LIKE 'Lennon%';
__istartswithCase-insensitive starts-with.name__istartswith='Lennon'SELECT ... WHERE name ILIKE 'Lennon%';
__endswithCase-sensitive ends-with.name__endswith='Lennon'SELECT ... WHERE name LIKE '%Lennon';
__iendswithCase-insensitive ends-with.name__iendswith='Lennon'SELECT ... WHERE name ILIKE '%Lennon'
__rangeRange test (inclusive).modified__range=('2022-01-01', '2022-03-31')SELECT ... WHERE modified BETWEEN '2022-01-01' and '2022-03-31';
__dateFor datetime fields, casts the value as date. Allows chaining additional field lookups.modified__date='2022-01-01'

modified__date__gte>='2022-01-01'		SELECT ... WHERE modified::date = '2022-01-01';

SELECT ... WHERE modified::date >= '2022-01-01';
__yearFor date and datetime fields, an exact year match. Allows chaining additional field lookups.modified__year=2022

modified__year__gte=2022		SELECT ... WHERE EXTRACT('year' FROM modified) = 2022;

SELECT ... WHERE EXTRACT('year' FROM modified) >= 2022;
__monthFor date and datetime fields, an exact month match. Allows chaining additional field lookups.modified__month=12

modified__month__gte=12		SELECT ... WHERE EXTRACT('month' FROM modified) = 12;

SELECT ... WHERE EXTRACT('month' FROM modified) >= 12;
__dayFor date and datetime fields, an exact day match. Allows chaining additional field lookups.modified__day=22

modified__day__gte=22		SELECT ... WHERE EXTRACT('day' FROM modified) = 22;

SELECT ... WHERE EXTRACT('day' FROM modified) >= 22;
__weekFor date and datetime fields, return the week number (1-52 or 53) according to ISO-8601, i.e., weeks start on a Monday and the first week contains the year’s first Thursday.modified__week=52
__week_dayFor date and datetime fields, a ‘day of the week’ match. Allows chaining additional field lookups.
Takes an integer value representing the day of week from 1 (Sunday) to 7 (Saturday).		modified__week_day=2

modified__week_day__gte=2
__quarterFor date and datetime fields, a ‘quarter of the year’ match. Allows chaining additional field lookups. Takes an integer value between 1 and 4 representing the quarter of the year.modified__quarter=2

modified__quarter__lte=2
__timeFor datetime fields, casts the value as time. Allows chaining additional field lookups.modified__time='14:30'

modified__time__gte>='14:30'
__hourFor datetime and time fields, an exact hour match. Allows chaining additional field lookups. Takes an integer between 0 and 23.modified__hour=12

modified__hour__gte=12		SELECT ... WHERE EXTRACT('hour' FROM modified) = 12;

SELECT ... WHERE EXTRACT('hour' FROM modified) >= 12;
__minuteFor datetime and time fields, an exact minute match. Allows chaining additional field lookups. Takes an integer between 0 and 59.modified__minute=33

modified__minute__gte=33		SELECT ... WHERE EXTRACT('minute' FROM modified) = 33;

SELECT ... WHERE EXTRACT('minute' FROM modified) >= 33;
__secondFor datetime and time fields, an exact second match. Allows chaining additional field lookups. Takes an integer between 0 and 59.modified__second=45

modified__second__gte=45		SELECT ... WHERE EXTRACT('second' FROM modified) = 45;

SELECT ... WHERE EXTRACT('second' FROM modified) >= 45;
__regexCase-sensitive regular expression match. The regular expression syntax is that of PostgreSQL.title__regex=r'^(An?|The) +'SELECT ... WHERE title ~ '^(An?|The) +';
__iregexCase-insensitive regular expression match. The regular expression syntax is that of PostgreSQL.title__iregex=r'^(an?|the) +'SELECT ... WHERE title ~* '^(an?|the) +';

Raw API Exclusion Filters

Filters can also EXCLUDE data by using the prefix "not" (that is a double underscore) ahead of the key name. For example, `/api/v1/raw/core/customfieldvalue?limit=10000&offset=0¬modified__gte=2022-05-24
`
will filter the results so that only the items with a modified date that is NOT greater than, or equal to, 2022-05-24 (i.e. less than) will be returned.

The not__ prefix can be used in conjuction with any of the operators listed above.

Updated over 1 year ago