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.

Operator	Definition	Example	Note
=	(column) equals (value)	topics_id = 1	eq 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 > 2	gt may be used instead of >
>=	(column) is greater than or equal to (value)	topics_group_id >= 2	gte 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"	Case sensitive
icontains	(column) contains (value)	subject icontains "foo"	Non-case sensitive
ncontains	(column) does not contain (value)	subject ncontains "bar"	Case sensitive
nicontains	(column) does not contain (value)	subject nicontains "bar"	Non-case sensitive
startswith	(column) starts with (value)	subject startswith "foo"	Case sensitive
istartswith	(column) starts with (value)	subject istartswith "foo"	Non-case sensitive
nstartswith	(column) does not start with (value)	subject nstartswith "foo"	Case sensitive
istartswith	(column) does not start with (value)	subject nistartswith "foo"	Non-case sensitive
endswith	(column) ends with (value)	subject endswith "foo"	Case sensitive
iendswith	(column) ends with (value)	subject iendswith "foo"	Non-case sensitive
nendswith	(column) does not end with (value)	subject nendswith "foo"	Case sensitive
niendswith	(column) does not end with (value)	subject niendswith "foo"	Non-case sensitive

Operator concatenation

You can use AND and OR to specify multiple conditions.

Operator	Definition	Example
AND	Both Condition 1 and Condition 2 are true	topics_id eq 1 AND subject eq foo
OR	Either Condition 1 or Condition 2 is true	topics_id eq 1 OR subject eq foo

Other notations

You can also use other notations such as parentheses.

Operator	Definition	Example
()	Condition prioritization	topics_id eq 1 AND (subject eq foo OR subject eq bar)
[]	Array of values	topics_id in [1, 2, 3]
,	Array delimiter for values	topics_id in [1, 2, 3]

Values

Type	Format	Example	Note
integer	%d	topics_id eq 1
float	%f	topics_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 ""
Empty array	:empty	ext_col_01 eq :empty	Please use this option if you want to filter out the state of not selecting anything in additional items for multiple choice. If you want to filter content with no tags selected, please use :R() search.
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.

Operator	Definition	Example
=:relatively	Equal to the specified value	timestamp =:relatively "today"
!=:relatively	Not equal to the specified value	timestamp !=:relatively "today"
>:relatively	Greater than the specified value	timestamp >:relatively "-9 hours"
>=:relatively	Greater than or equal to the specified value	timestamp >=:relatively "-10 hours"
<:relatively	Less than the specified value	timestamp <:relatively "1 week"
<=:relatively	Less than or equal to the specified value	timestamp <=: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)

Symbol	Format
daytext	weekday weekdays
number	[+-]?[0-9]+
ordinal	first second third fourth fifth sixth seventh eighth ninth tenth eleventh twelfth next last previous this
reltext	next last previous this
space	[ \t]+
unit	sec second s, min minute, hour weeks weekday weekdays day fortnight forthnight month year

Common input expressions are shown below with examples.

Format	Definition	Example
yesterday	The day before (starting from midnight)	yesterday 14:00
midnight	00:00:00
today	The current day (starting from midnight)
now	The current time
noon	12:00:00	yesterday noon
tomorrow	The next day (starting from midnight)
back of (hour)	15 minutes past the specified hour	back of 7pm, back of 15
front of (hour)	15 minutes before the specified hour	front of 5am, front of 23
first day of (month)	First day of the specified month	first day of January 2008
last day of (month)	Last day of the specified month	last day of next month
(ordinal) (dayname) of	The x-th day-of-week of the specified month	first sat of July 2008
last (dayname) of	The final day-of-week of the specified month	last 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 string	fifth day, second month
ago	Subtracts date and time input from current date and time	2 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-week	Monday
(reltext) week	Handles the special format: weekday + (last/this/next) week	Monday next week

:File() Search

Searches are executed based on the presence or absence of images.

The searchable items are as follows:

• Image (upload to KurocoFiles)
• File (upload to KurocoFiles)
• File (upload to GCS)
• File (upload to S3)

Examples of filter specifications:

• A file exists in ext_1: :File(exists(ext_1))
• A file does not exist in ext_1: :File(nexists(ext_1))

caution

The search using :File() is enabled on sites where the extended content field is ext_1.
Please note that it does not work on sites where the response of the extended field is in the format ext_col_01.

:R() Search

You can execute searches based on the information of content or tags linked to related information.
For detailed usage, please refer to the following.

info

• Filter function with conditions based on related data

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.

Parameter	Definition	Example
pageID	Specify the page number to return to	pageID=1
perPage	Number of results per page	perPage=20
filter_lang	Language used for filtering	filter_lang=en
_lang	Language of the content to be responded with	_lang=en