Skip to content

Filtering

Searching resources is performed in URL. The filtering is always composed by URL segment that defines column to filter on and next URL segment that is value. Here's an example of a URL that uses all filtering options

See proper section for more details: Exact Value, Or, And, Search by Substring, Search by Ranges and Advanced Data Navigation

Data Types

  • String use urlencoding in case of non-ascii characters
  • Number use integers or float with "." (dot) for decimal separator
  • NULL use the value <null> in URL queries
  • Date use ISO format (YYYY-MM-DD)

  • Datetime use ISO formats

    • YYYY-MM-DD hh:mm:ss
    • YYYY-MM-DD hh:mm:ss.uuuuuu
    • YYYY-MM-DDThh:mm:ss
    • YYYY-MM-DDThh:mm:ss.uuuuuu

where:
YYYY - four digit year e.g. 2018
MM - two digit month e.g. 12
DD - two digit day e.g. 31
hh - two digit hour e.g. 23
mm - two digit minute e.g. 59
ss - two digit second e.g. 59
uuuuuu - six digit microsecond e.g. 999999

Exact Value

The simplest filtering is by exact comparison.

Usage:

/[column_name]/[value]

Examples:
https://demo.slashdb.com/db/Chinook/Customer/Country/USA - get Customers from USA

OR

You can separate values with "," (comma) to compare with multiple values. This effectively works like an "or".

Usage:

/[column_name]/[value1],[value2]

Examples:

https://demo.slashdb.com/db/Chinook/Customer/Country/USA,Canada - get Customers from USA or Canada

AND

You can combine filtering on multiple columns which effectively works like and "and". You can combine as many, as you need.

Usage:

/[column_A]/[value_for_A]/[column_B]/[value_for_B]

Examples:

https://demo.slashdb.com/db/Chinook/Customer/Country/USA/City/New York - get Customers from New York, USA.

NOT (negation)

Negation can be applied to any filter (i.e. exact value, substring, etc.) to obtain records other than the ones matching the given filter. Prefix the filtered field with the ~ (tilde) character to invert the filtering condition for that field.

Usage:

/~[column_name]/[filter]

Examples:

https://demo.slashdb.com/db/Chinook/Customer/~Country/USA - get Customers from anywhere except the USA.

https://demo.slashdb.com/db/Chinook/Customer/~Country/USA,Canada - get Customers from anywhere except the USA or Canada.

https://demo.slashdb.com/db/Chinook/Track/~TrackId/1..3495 - get Tracks with TrackId outside of the range of 1 to 3495.

Search by Substring

It's also possible to search by matching substring. In general "*" (asterisk) means any characters. It's also possible to search asterisk literally but then you need to override default wildcard character.

Usage:

/[column_name]/[starts_with_string]*
/[column_name]/*[ends_with_string]
/[column_name]/[starts_with_string]*[ends_with_string]
/[column_name]/*[contains_string]*

Examples:

https://demo.slashdb.com/db/Chinook/Customer/LastName/M* - get only Customer with LastName starting with letter M
https://demo.slashdb.com/db/Chinook/Customer/LastName/M*r - get only Customer with LastName starting with letter "M" and ending with letter "r".
https://demo.slashdb.com/db/Chinook/Customer/City/*ar* - get only Customers from Cities that have "ar" somewhere in their name.

Search by Ranges

For numeric values (integer, float, date) can filter by a range.

  • Greater or equal
  • Less or equal
  • Between

Usage:

/[column_name]/[value]..
/[column_name]/..[value_to]
/[column_name]/[value_from]..[value_to]

Examples:

https://demo.slashdb.com/db/Chinook/Invoice/Total/9.91.. - get Invoices of Total value ≥ 9.91

https://demo.slashdb.com/db/Chinook/Invoice/Total/..1.98 - get Invoices of Total value ≤ 1.98

https://demo.slashdb.com/db/Chinook/Invoice/CustomerId/1..5.html - get Invoices of Customer with CustomerId between 1 and 5.

Search by Multiple Ranges

This feature is combination of filter by ranges and OR filtering. It allows to filter using multiple values or ranges. It works with numeric columns only i.e. numbers, date, time and datetime.

Usage:

/[column_name]/[value1],[value2]
/[column_name]/[value1]..[value2],[value3]
/[column_name]/[value1]..[value2],[value3]..[value4]
/[column_name]/..[value1],[value2],[value3]

Examples:

https://demo.slashdb.com/db/Chinook/Invoice/CustomerId/1..3,5.html - get Invoices of Customer with CustomerId equals 1, 2, 3 or 5.

https://demo.slashdb.com/db/Chinook/Invoice/CustomerId/1..3,5..7.html - get Invoices of Customer with CustomerId between 1 and 3, or 5 and 7.

https://demo.slashdb.com/db/Chinook/Invoice/CustomerId/..3,5,7.html - get Invoices of Customer with CustomerId less than 3, or equals 5 or 7.

Filter NULL values

To filter on NULL values, use the <null> keyword in URLs.

If you want to use a custom placeholder, you can use the nullStr query string parameter.

Examples:

https://demo.slashdb.com/db/Chinook/Customer/State/<null>.html - get Customer records where the State column is NULL.

https://demo.slashdb.com/db/Chinook/Invoice/CustomerId/1..3,<null>.html - get Invoices of Customer where CustomerId equals 1, 2, 3 or NULL.

https://demo.slashdb.com/db/Chinook/Employee/ReportsTo/1..3,$null.html?nullStr=$null - get Employee records where ReportsTo equals 1, 2, 3 or NULL using a placeholder value of $null.

Column filter

It's possible to select only certain columns. See section about selecting single column or multiple columns.