Using the filter query function

This section explains how to use the search function.

Overview

Define the search feature using a function called the "filter query", which enables you to specify the search criteria like with SQL.

For example, enter the following expression to search for data from June 01 to June 30, 2021 in the timestamp field:

?filters={%22filter%22:%22(timestamp%20%3E=%20\%222021-06-01%2000:00\%22%20AND%20timestamp%20%3C=%20\%222021-06-30%2023:59\%22)%22,%22order%22:%22%22}

The filters parameter is URL-encoded with the following content in JSON format.

{
    "filter": "(timestamp >= \"2021-06-01 00:00\" AND timestamp <= \"2021-06-30 23:59\")",
    "order": ""
}

The filter parameter

Define the filter parameter using this basic format: (column) (operator) (value)

  • (column): The specified data. In the above example, the column is timestamp.
  • (operator): The relation between the column and the value. For example, this can be = or >=.
    (See: Operators below for details.)
  • (value): The search condition value. This can be a number or a string.

Relative date mode

The relative data mode method enables you to search by the date and time relative to today, yesterday, or a certain number of hours ago. It takes this basic format: (column) (operator):relatively (value)

  • (column): Same as in the regular filter parameter.
  • (operator): Same as in the regular filter parameter, but some operators may not be available.

For example, you can retrieve today's data using the following search condition: timestamp >=:relatively "today"

(See: Operators and values in relative date mode for the allowed operators and values in this mode.)

Operators

The following operators are allowed in the filter query.

OperatorDefinitionExampleNote
=(column) equals (value)topics_id = 1eq may be used instead of =
!=(column) does not equal (value)ext_col_01 != ""ne may be used instead of !=
<(column) is less than (value)inst_ymdhi < "2020-08-01"lt may be used instead of <
<=(column) is less than or equal to (value)inst_ymdhi <= "2020-08-01"lte may be used instead of <=
>(column) is greater than (value)topics_group_id > 2gt may be used instead of >
>=(column) is greater than or equal to (value)topics_group_id >= 2gte may be used instead of >=
in(column) contains any part of (value)ext_col_01 in ["A", "B", "C"]
nin(column) does not contain any part of (value)ext_col_01 nin ["A", "B", "C"]
all(column) contains all parts of (value)secure_level all [1, 2](value) must be an array
nall(column) does not contain all parts of (value)secure_level nall [1, 2](value) must be an array
contains(column) contains (value)subject contains "foo"Non-case sensitive
icontains(column) contains (value)subject icontains "foo"Case sensitive
ncontains(column) does not contain (value)subject ncontains "bar"Non-case sensitive
nincontains(column) does not contain (value)subject nincontains "bar"Case sensitive
startswith(column) starts with (value)subject startswith "foo"Non-case sensitive
istartswith(column) starts with (value)subject istartswith "foo"Case sensitive
nstartswith(column) does not start with (value)subject nstartswith "foo"Non-case sensitive
istartswith(column) does not start with (value)subject nistartswith "foo"Case sensitive
endswith(column) ends with (value)subject endswith "foo"Non-case sensitive
iendswith(column) ends with (value)subject iendswith "foo"Case sensitive
nendswith(column) does not end with (value)subject nendswith "foo"Non-case sensitive
niendswith(column) does not end with (value)subject niendswith "foo"Case sensitive

Operator concatenation

You can use AND and OR to specify multiple conditions.

OperatorDefinitionExample
ANDBoth Condition 1 and Condition 2 are truetopics_id eq 1 AND subject eq foo
OREither Condition 1 or Condition 2 is truetopics_id eq 1 OR subject eq foo

Other notations

You can also use other notations such as parentheses.

OperatorDefinitionExample
()Condition prioritizationtopics_id eq 1 AND (subject eq foo OR subject eq bar)
[]Array of valuestopics_id in [1, 2, 3]
,Array delimiter for valuestopics_id in [1, 2, 3]

Values

TypeFormatExampleNote
integer%dtopics_id eq 1
float%ftopics_id > 1.00
date"Y-m-d"inst_ymdhi < "2020-08-01"
time"H:m:s"
"H:m"
post_time > "12:30:00"
"H:m" post_time > "12:30"
datetime"Y-m-d H:i"
"Y-m-d H:i:s"
"Y-m-d H:i:s O"
update_ymdhi < "2020-08-01 12:00"
update_ymdhi < "2020-08-01 12:00:00"
update_ymdhi < "2020-08-01 12:00:00 +0900"
date + time"Y/m/d H:i:s"ymd_time > "2021/09/30 12:30:00"To enable this value type, select the checkbox in the "Publish timestamp" field of the content structure.
(See: Content structure editor for details.)
Empty value""ext_col_01 eq ""
String"%s"
%s
subject eq "TITLE"
subject eq TITLE
Enclosing any value expression in double quotation marks " treats it as a string, except for date/time/datetime expressions.
(Note: Expressions that are not enclosed in double quotation marks, do not match any other types, and contain no spaces or special characters are also treated as strings.)

Operators and values in relative date mode

Operators in relative date mode

The following operators are allowed in relative date mode.

OperatorDefinitionExample
=:relativelyEqual to the specified valuetimestamp =:relatively "today"
!=:relativelyNot equal to the specified valuetimestamp !=:relatively "today"
>:relativelyGreater than the specified valuetimestamp >:relatively "-9 hours"
>=:relativelyGreater than or equal to the specified valuetimestamp >=:relatively "-10 hours"
<:relativelyLess than the specified valuetimestamp <:relatively "1 week"
<=:relativelyLess than or equal to the specified valuetimestamp <=:relatively "last Monday"

Values in relative date mode

The relative date mode is compatible with the strtotime() PHP function.

The following formats are allowed (adapted from PHP: Relative Formats - Manual)

SymbolFormat
daytextweekday weekdays
number[+-]?[0-9]+
ordinalfirst second third fourth fifth sixth seventh eighth ninth tenth eleventh twelfth
next last previous this
reltextnext last previous this
space[ \t]+
unitsec second s, min minute, hour weeks weekday weekdays day fortnight forthnight month year

Common input expressions are shown below with examples.

FormatDefinitionExample
yesterdayThe day before (starting from midnight)yesterday 14:00
midnight00:00:00
todayThe current day (starting from midnight)
nowThe current time
noon12:00:00yesterday noon
tomorrowThe next day (starting from midnight)
back of (hour)15 minutes past the specified hourback of 7pm, back of 15
front of (hour)15 minutes before the specified hourfront of 5am, front of 23
first day of (month)First day of the specified monthfirst day of January 2008
last day of (month)Last day of the specified monthlast day of next month
(ordinal) (dayname) ofThe x-th day-of-week of the specified monthfirst sat of July 2008
last (dayname) ofThe final day-of-week of the specified monthlast sat of July 2008
(number) (unit)Relative time expression where the value is a number+5 weeks, 12 day, -7 weekdays
(ordinal) (unit)Relative time expression where the value is a stringfifth day, second month
agoSubtracts date and time input from current date and time2 days ago, 8 days ago 14:00, 2 months 5 days ago, 2 months ago 5 days, 2 days ago
(dayname)The nearest upcoming day-of-weekMonday
(reltext) weekHandles the special format: weekday + (last/this/next) weekMonday next week

The order parameter

The order parameter specifies the sort order of the results. It takes this format: field name=(ascending/descending).

  • Ascending: Starts from the smallest data value and increases. Written as ASC (e.g., timestamp=ASC).
  • Descending: Starts from the largest data value and decreases. Written as DESC (e.g., timestamp=DESC).

Other parameters

Some other parameters are allowed when executing the search.

ParameterDefinitionExample
pageIDSpecify the page number to return topageID=1
perPageNumber of results per pageperPage=20