Query Operators and Functions

Query Operators

OPERATOR	SAMPLE	RESPONDED RECORDS
=	string_0 = "test"	Records where the field line value before the operator and the string after the operator match.
!=	string_0 != "test"	Records where the field line value before the operator and the string after the operator don't match.
>	number_0 > 10	Records where the field line value before the operator is greater than the value after the operator.
<	number_0 < 10	Records where the field line value before the operator is less than the value after the operator.
>=	number_0 >= 10	Records where the field line value before the operator is greater than or equal to the value after the operator.
<=	number_0 <= 10	Records where the field line value before the operator is less than or equal to the value after the operator.
in	dropdown_0 in ("A", "B")	Records where the field line value before the operator matches any of the strings listed in the brackets after the operator. This expression can be used to find what choices have been selected for fields that have multiple selections, such as Drop-down fields and Radio button fields. The sample on the left responds records where the options "A" or "B" have been selected for the "dropdown_0" field.
not in	dropdown_0 not in ("A", "B")	Records where the field line value before the operator don't matches any of the strings listed in the brackets after the operator. This expression can be used to find what choices have not been selected for fields that have multiple selections, such as Drop-down fields and Radio button fields. The sample on the left responds records where the options "A" or "B" haven't been selected for the "dropdown_0" field.
like	Single_line_text_0 like "test"	Records where the string after the operator is included in the field line value before the operator. If an Attachment field is specified, records where the string after the operator is included in the file name or the contents of the file will be responded. Refer to the Are there any symbols that cannot be used for search or filtering? article from the Kintone Help for information about the symbols that cannot be used.
not like	Single_line_text_0 not like "test"	Records where the string after the operator is not included in the field line value after the operator. Refer to the Are there any symbols that cannot be used for search or filtering? article from the Kintone Help for information about the symbols that cannot be used.
or	number_0 < 10 or number_0 > 20	Records where the expression on either side of the or is true. The sample on the left responds records where the value of "number_0" is less than 10, or greater than 20.
and	number_0 >= 10 and number_0 <= 20	Records where the expression on both sides of the and is true. The sample on the left responds records where the value of "number_0" is greater than or equal to 10, and less than or equal to 20.

• Field codes are to be placed before the operator, not after it.
• Formulas can be grouped by using "()" brackets, for example: (number_0 >= 10 and number_0 <= 20) or (number_1 >= 100 and number_1 <= 200)

Query Functions

Available Operators and Functions for each field

Specifying fields within Related Record fields

• Specify fields within the Related Records field with the following format:
  relatedrecords.specifiedfield where relatedrecords is the field line of the Related Records field, and specifiedfield is the field line of the specified field.

Query Options

The below options can be used in conjunction.

Escape Characters

Query sensitive parameters such as " (Double Quotations) and \ (Backslashes) must be properly escaped using a \ backslash when included in a query string for the following fields:

• Text
• Text area
• Rich text
• Check box
• Radio button
• Drop-down
• Multi-choice
• Status

Sample Queries Containing Escape Characters

In this example, a Check box field with the field code Checkbox is queried. The value of the Check box is sample"1", which includes double quotations and therefore must be escaped properly.

1

Checkbox in ("sample\"1\"")

Similarly, this Check Box field has a value of sample\2\, which requires the backslash characters to be escaped.

1

Checkbox in ("sample\\2\\")

When using the Kintone REST API, sensitive characters must be escaped within the request body JSON as well and require additional backslashes. In the example below, for sending the value sample"1" in a request body, note that JavaScript requires a backslash to escape, in addition to the escape required for Kintone query strings.

1
2
3
4

const body = {
  app: kintone.app.getId(),
  query: 'Checkbox in ("sample\\"1\\"")'
};

Similarly, sending the value sample\1\ also requires one escape character for JavaScript and one for Kintone's query string.

1
2
3
4

const body = {
  app: kintone.app.getId(),
  query: 'Checkbox in ("sample\\\\1\\\\")'
};

Sample Queries

Sample 1

• the field Text includes the string "sample"
• the field Text_area includes the string "sample"
• results are listed in descending order of the field Created_datetime

1

Text like "sample" and Text_area like "sample" order by Created_datetime desc

Sample 2

• the field Drop_down includes the values "value1" or "value2"
• the field Multi_choice includes the value "value3" or the field Radio_button includes the value "value4"

1

Drop_down in ("value1", "value2") and (Multi_choice in ("value3") or Radio_button in ("value4"))

Sample 3

• the field Time is greater than the value "10:00"
• the field Time is less than the value "19:00"
• the field Created_datetime is equal to today's date
• results are listed in ascending order of the Record number field
• only up to the first 10 results are outputted

1

Time > 10:00 and Time < 19:00 and Created_datetime = TODAY() order by $id asc limit 10

Sample 4

• The user running the API call is included in the field User_selection
• The priority department of the user running the API call is not included in the field Department_selection

1

User_selection in (LOGINUSER()) and Department_selection not in (PRIMARY_ORGANIZATION())

Sample 5

• The field company_name within the field Company_DB (a Related Records field) has a value equal to "kintone"
• The field location within the field Company_DB (a Related Records field) includes the string "San Francisco"

1

Company_DB.company_name in ("kintone") and Company_DB.location like "San Francisco"