How to Write cli-kintone Queries

Contents

Overview

This section describes how to write cli-kintone queries.

Conditions

To specify conditions, use the --condition option. All values must be enclosed in double quotation marks ". Functions do not need to be enclosed.
Operators and functions can be used in combination.

1

--condition "field code, operator, value, or function"

When specifying strings or options for values, surround them with \" (double quotations escaped with a backslash).

1

--condition "Customer = \"Kintone\""

Operators

Operators Example Description
= --condition "string_0 = \"test\"" Records where the field line value before the operator and the string after the operator match.
!= --condition "string_0 != \"test\"" Records where the field line value before the operator and the string after the operator don't match.
> --condition "number_0 > 10" Records where the field line value before the operator is greater than the value after the operator.
< --condition "number_0 < 10" Records where the field line value before the operator is less than the value after the operator.
>= --condition "number_0 > = 10" Records where the field line value before the operator is greater than or equal to the value after the operator.
<= --condition "number_0 < = 10" Records where the field line value before the operator is less than or equal to the value after the operator.
in --condition "Drop-down_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.
not in --condition "Drop-down_0 not in (\"A\", \"B\")" Records where the field line value before the operator don't match any of the strings listed in the brackets after the operator.
like --condition "string_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.

For more information about the symbols that cannot be used, refer to the following article:
Are there any symbols that cannot be used for search or filtering? (External link)
not like --condition "string_0 not like \"test\"" Records where the string after the operator is not included in the field line value after the operator.

For more information about the symbols that cannot be used, refer to the following article:
Are there any symbols that cannot be used for search or filtering? (External link)
or --condition "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 --condition "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.

Notes

  • To include fields in Tables and Related records in your query, use in and not in operators instead of = and != operators.
  • A query search is preformed for each keyword. For more information, refer to the following article:
    Points to Note When Entering Search Keywords (External link)

Functions

Function Name Example Description
LOGINUSER() --condition "Created_by in (LOGINUSER())" Converts to the user initiating the query.
PRIMARY_ORGANIZATION() --condition "Department in (PRIMARY_ORGANIZATION())" Converts to the Priority Department of the user initiating the query. If the priority department is not set, the condition is ignored.
NOW() --condition "Created_datetime = NOW()" Converts to the date and time of when the query was initiated.
TODAY() --condition "Created_datetime = TODAY()" Converts to the date of when the query was initiated.
YESTERDAY() --condition "Created_datetime = YESTERDAY()" Converts to the date 1 day before the query was initiated.
TOMORROW() --condition "Datetime = TOMORROW()" Converts to the date 1 day after the query was initiated.
FROM_TODAY(number, unit of time) --condition "Created_datetime < FROM_TODAY(5, DAYS)" Converts to the date specified by the period starting from today. The below periods can be specified:
  • DAYS
  • WEEKS
  • MONTHS
  • YEARS
THIS_WEEK(Day of the week) --condition "Created_datetime = THIS_WEEK()" Converts to the specified day in this week. If nothing is specified, it converts to all days in this week.
The following days of the week can be set:
  • SUNDAY
  • MONDAY
  • TUESDAY
  • WEDNESDAY
  • THURSDAY
  • FRIDAY
  • SATURDAY
LAST_WEEK(Day of the week) --condition "Created_datetime = LAST_WEEK()" Converts to the specified day in the previous week. If nothing is specified, it converts to all days in the previous week.
The following days of the week can be set:
  • SUNDAY
  • MONDAY
  • TUESDAY
  • WEDNESDAY
  • THURSDAY
  • FRIDAY
  • SATURDAY
NEXT_WEEK(Day of the week) --condition "Datetime = NEXT_WEEK()" Converts to the specified day in the next week. If nothing is specified, it converts to all days in the next week.
The following days of the week can be set:
  • SUNDAY
  • MONDAY
  • TUESDAY
  • WEDNESDAY
  • THURSDAY
  • FRIDAY
  • SATURDAY
THIS_MONTH(day of the month) --condition "Created_datetime = THIS_MONTH()" Converts to the month of when the query was initiated.
The following can be specified:
  • Integers from 1-31 : the day of the month. If the day of the month does not exist, it will convert to all days of the next month.
  • LAST : the last day of the month.
If nothing is specified, it converts to all days of this month.
LAST_MONTH(day of the month) --condition "Created_datetime = LAST_MONTH()" Converts to the previous month of when the query was initiated.
The following can be specified:
  • Integers from 1-31 : the day of the previous month. If the day of the month does not exist, it will convert to all days of the month to follow.
  • LAST : the last day of the previous month.
If nothing is specified, it converts to all days of the previous month.
NEXT_MONTH(day of the month) --condition "date and time = NEXT_MONTH()" Converts to the next month of when the query was initiated.
The following can be specified:
  • Integers from 1-31 : the day of the next month. If the day of the month does not exist, it will convert to all days of the month to follow.
  • LAST : the last day of the next month.
If nothing is specified, it converts to all days of the next month.
THIS_YEAR() --condition "Created_datetime = THIS_YEAR()" Converts to the year of when the query was initiated.
LAST_YEAR() --condition "Created_datetime = LAST_YEAR()" Converts to the year before the query was initiated.
NEXT_YEAR() --condition "Datetime = NEXT_YEAR()" Converts to the year after the query was initiated.

Available Operators and Functions for Each Field

Not all operators and functions can be used in every field. For more details on available operators and functions for each field, refer to the following article section:
Query string: Available Operators and Functions for each field

Display order

To specify display order of records, use the --order-by option.

1

--order-by "Updated_datetime asc"

Specify asc after the field code to sort in ascending order and desc for descending order.
To sort by multiple items, separate each Field_code Sorting_order pair with commas.

1

--order-by "field_code_1 desc, field_code_2 asc"

For fields that can be sorted using --order-by, refer to the following article:
Which fields and items can be used for "Sort by"? (External link)

Escape Characters

Query sensitive parameters such as " (double quotations) and \ (backslashes) must be 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 correctly.

1

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

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

1

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

Sample Queries

For sample queries, refer to following article:
Query string