# groonga - An open-source fulltext search engine and column store.

- 日本語

# 7.3.54. `select`

## 7.3.54.1. Summary

`select` searches records that are matched to specified conditions from a table and then outputs them.

`select` is the most important command in groonga. You need to understand `select` to use the full power of Groonga.

## 7.3.54.2. Syntax

This command takes many parameters.

The required parameter is only `table`. Other parameters are optional:

    select table
           [match_columns=null]
           [query=null]
           [filter=null]
           [scorer=null]
           [sortby=null]
           [output_columns="_id, _key, *"]
           [offset=0]
           [limit=10]
           [drilldown=null]
           [drilldown_sortby=null]
           [drilldown_output_columns="_key, _nsubrecs"]
           [drilldown_offset=0]
           [drilldown_limit=10]
           [cache=yes]
           [match_escalation_threshold=0]
           [query_expansion=null]
           [query_flags=ALLOW_PRAGMA|ALLOW_COLUMN]
           [query_expander=null]
           [adjuster=null]
           [drilldown_calc_types=NONE]
           [drilldown_calc_target=null]
           [sort_keys=null]
           [drilldown_sort_keys=null]

`select` has the following named parameters for advanced drilldown:

> - `drilldowns[${LABEL}].keys=null`
> - `drilldowns[${LABEL}].sortby=null`
> - `drilldowns[${LABEL}].output_columns="_key, _nsubrecs"`
> - `drilldowns[${LABEL}].offset=0`
> - `drilldowns[${LABEL}].limit=10`
> - `drilldowns[${LABEL}].calc_types=NONE`
> - `drilldowns[${LABEL}].calc_target=null`

Deprecated since version 6.0.3: `drilldown[...]` syntax is deprecated, Use `drilldowns[...]` instead.

You can use one or more alphabets, digits, `_` and `.` for `${LABEL}`. For example, `parent.sub1` is a valid `${LABEL}`.

Parameters that have the same `${LABEL}` are grouped.

For example, the following parameters specify one drilldown:

> - `--drilldowns[label].keys column`
> - `--drilldowns[label].sortby -_nsubrecs`

The following parameters specify two drilldowns:

> - `--drilldowns[label1].keys column1`
> - `--drilldowns[label1].sortby -_nsubrecs`
> - `--drilldowns[label2].keys column2`
> - `--drilldowns[label2].sortby _key`

## 7.3.54.3. Usage

Let's learn about `select` usage with examples. This section shows many popular usages.

Here are a schema definition and sample data to show usage.

Execution example:

    table_create Entries TABLE_HASH_KEY ShortText
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    column_create Entries content COLUMN_SCALAR Text
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    column_create Entries n_likes COLUMN_SCALAR UInt32
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    column_create Entries tag COLUMN_SCALAR ShortText
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    table_create Terms TABLE_PAT_KEY ShortText --default_tokenizer TokenBigram --normalizer NormalizerAuto
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    column_create Terms entries_key_index COLUMN_INDEX|WITH_POSITION Entries _key
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    column_create Terms entries_content_index COLUMN_INDEX|WITH_POSITION Entries content
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    load --table Entries
    [
    {"_key":    "The first post!",
     "content": "Welcome! This is my first post!",
     "n_likes": 5,
     "tag": "Hello"},
    {"_key":    "Groonga",
     "content": "I started to use Groonga. It's very fast!",
     "n_likes": 10,
     "tag": "Groonga"},
    {"_key":    "Mroonga",
     "content": "I also started to use Mroonga. It's also very fast! Really fast!",
     "n_likes": 15,
     "tag": "Groonga"},
    {"_key":    "Good-bye Senna",
     "content": "I migrated all Senna system!",
     "n_likes": 3,
     "tag": "Senna"},
    {"_key":    "Good-bye Tritonn",
     "content": "I also migrated all Tritonn system!",
     "n_likes": 3,
     "tag": "Senna"}
    ]
    # [[0, 1337566253.89858, 0.000355720520019531], 5]

There is a table, `Entries`, for blog entries. An entry has title, content, the number of likes for the entry and tag. Title is key of `Entries`. Content is value of `Entries.content` column. The number of likes is value of `Entries.n_likes` column. Tag is value of `Entries.tag` column.

`Entries._key` column and `Entries.content` column are indexed using `TokenBigram` tokenizer. So both `Entries._key` and `Entries.content` are fulltext search ready.

OK. The schema and data for examples are ready.

### 7.3.54.3.1. Simple usage

Here is the most simple usage with the above schema and data. It outputs all records in `Entries` table.

Execution example:

    select Entries
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         1,
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         5,
    #         "Hello"
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ],
    #       [
    #         4,
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         3,
    #         "Senna"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ]
    #     ]
    #   ]
    # ]

Why does the command output all records? There are two reasons. The first reason is that the command doesn't specify any search conditions. No search condition means all records are matched. The second reason is that the number of all records is 5. `select` command outputs 10 records at a maximum by default. There are only 5 records. It is less than 10. So the command outputs all records.

### 7.3.54.3.2. Search conditions

Search conditions are specified by `query` or `filter`. You can also specify both `query` and `filter`. It means that selected records must be matched against both `query` and `filter`.

#### 7.3.54.3.2.1. Search condition: `query`

`query` is designed for search box in Web page. Imagine a search box in google.com. You specify search conditions for `query` as space separated keywords. For example, `search engine` means a matched record should contain two words, `search` and `engine`.

Normally, `query` parameter is used for specifying fulltext search conditions. It can be used for non fulltext search conditions but `filter` is used for the propose.

`query` parameter is used with `match_columns` parameter when `query` parameter is used for specifying fulltext search conditions. `match_columns` specifies which columnes and indexes are matched against `query`.

Here is a simple `query` usage example.

Execution example:

    select Entries --match_columns content --query fast
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command searches records that contain a word `fast` in `content` column value from `Entries` table.

`query` has query syntax but its deatils aren't described here. See Query syntax for datails.

#### 7.3.54.3.2.2. Search condition: `filter`

`filter` is designed for complex search conditions. You specify search conditions for `filter` as ECMAScript like syntax.

Here is a simple `filter` usage example.

Execution example:

    select Entries --filter 'content @ "fast" && _key == "Groonga"'
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         1
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command searches records that contain a word `fast` in `content` column value and has `Groonga` as `_key` from `Entries` table. There are three operators in the command, `@`, `&&` and `==`. `@` is fulltext search operator. `&&` and `==` are the same as ECMAScript. `&&` is logical AND operator and `==` is equality operator.

`filter` has more operators and syntax like grouping by `(...)` its details aren't described here. See Script syntax for datails.

### 7.3.54.3.3. Paging

You can specify range of outputted records by `offset` and `limit`. Here is an example to output only the 2nd record.

Execution example:

    select Entries --offset 1 --limit 1
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ]
    #     ]
    #   ]
    # ]

`offset` is zero-based. `--offset 1` means output range is started from the 2nd record.

`limit` specifies the max number of output records. `--limit 1` means the number of output records is 1 at a maximium. If no records are matched, `select` command outputs no records.

### 7.3.54.3.4. The total number of records

You can use `--limit 0` to retrieve the total number of recrods without any contents of records.

Execution example:

    select Entries --limit 0
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ]
    #     ]
    #   ]
    # ]

`--limit 0` is also useful for retrieving only the number of matched records.

### 7.3.54.3.5. Drilldown

You can get additional grouped results against the search result in one `select`. You need to use two or more `SELECT``s in SQL but ``select` in Groonga can do it in one `select`.

This feature is called as drilldown in Groonga. It's also called as faceted search in other search engine.

For example, think about the following situation.

You search entries that has `fast` word:

Execution example:

    select Entries --filter 'content @ "fast"'
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ]
    #     ]
    #   ]
    # ]

You want to use `tag` for additional search condition like `--filter 'content @ "fast" && tag == "???"`. But you don't know suitable tag until you see the result of `content @ "fast"`.

If you know the number of matched records of each available tag, you can choose suitable tag. You can use drilldown for the case:

Execution example:

    select Entries --filter 'content @ "fast"' --drilldown tag
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ]
    #     ],
    #     [
    #       [
    #         1
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "Groonga",
    #         2
    #       ]
    #     ]
    #   ]
    # ]

`--drilldown tag` returns a list of pair of available tag and the number of matched records. You can avoid "no hit search" case by choosing a tag from the list. You can also avoid "too many search results" case by choosing a tag that the number of matched records is few from the list.

You can create the following UI with the drilldown results:

> - Links to narrow search results. (Users don't need to input a search query by their keyboard. They just click a link.)

Most EC sites use the UI. See side menu at Amazon.

Groonga supports not only counting grouped records but also finding the maximum and/or minimum value from grouped records, summing values in grouped records and so on. See Drilldown related parameters for details.

## 7.3.54.4. Parameters

This section describes all parameters. Parameters are categorized.

### 7.3.54.4.1. Required parameters

There is a required parameter, `table`.

#### 7.3.54.4.1.1. `table`

Specifies a table to be searched. `table` must be specified.

If nonexistent table is specified, an error is returned.

Execution example:

    select Nonexistent
    # [
    #   [
    #     -22,
    #     1337566253.89858,
    #     0.000355720520019531,
    #     "invalid table name: <Nonexistent>",
    #     [
    #       [
    #         "grn_select",
    #         "proc.c",
    #         1217
    #       ]
    #     ]
    #   ]
    # ]

### 7.3.54.4.2. Search related parameters

There are search related parameters. Typically, `match_columns` and `query` parameters are used for implementing a search box. `filter` parameters is used for implementing complex search feature.

If both `query` and `filter` are specified, selected records must be matched against both `query` and `filter`. If both `query` and `filter` aren't specified, all records are selected.

#### 7.3.54.4.2.1. `match_columns`

Specifies the default target column for fulltext search by `query` parameter value. A target column for fulltext search can be specified in `query` parameter. The difference between `match_columns` and `query` is whether weight and score function are supported or not. `match_columns` supports them but `query` doesn't.

Weight is relative importance of target column. A higher weight target column gets more hit score rather than a lower weight target column when a record is matched by fulltext search. The default weight is 1.

Here is a simple `match_columns` usage example.

Execution example:

    select Entries --match_columns content --query fast --output_columns '_key, _score'
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_score",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "Groonga",
    #         1
    #       ],
    #       [
    #         "Mroonga",
    #         2
    #       ]
    #     ]
    #   ]
    # ]

`--match_columns content` means the default target column for fulltext search is `content` column and its weight is 1. `--output_columns '_key, _score'` means that the `select` command outputs `_key` value and `_score` value for matched records.

Pay attention to `_score` value. `_score` value is the number of matched counts against `query` parameter value. In the example, `query` parameter value is `fast`. The fact that `_score` value is 1 means that `fast` appers in `content` column only once. The fact that `_score` value is 2 means that `fast` appears in `content` column twice.

To specify weight, `column * weight` syntax is used. Here is a weight usage example.

Execution example:

    select Entries --match_columns 'content * 2' --query fast --output_columns '_key, _score'
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_score",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "Groonga",
    #         2
    #       ],
    #       [
    #         "Mroonga",
    #         4
    #       ]
    #     ]
    #   ]
    # ]

`--match_columns 'content * 2'` means the default target column for fulltext search is `content` column and its weight is 2.

Pay attention to `_score` value. `_score` value is doubled because weight is 2.

You can specify one or more columns as the default target columns for fulltext search. If one or more columns are specified, fulltext search is done for all columns and scores are accumulated. If one of the columns is matched against `query` parameter value, the record is treated as matched.

To specify one or more columns, `column1 * weight1 || column2 * weight2 || ...` syntax is used. `* weight` can be omitted. If it is omitted, 1 is used for weight. Here is a one or more columns usage example.

Execution example:

    select Entries --match_columns '_key * 10 || content' --query groonga --output_columns '_key, _score'
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         1
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_score",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "Groonga",
    #         11
    #       ]
    #     ]
    #   ]
    # ]

`--match_columns '_key * 10 || content'` means the default target columns for fulltext search are `_key` and `content` columns and `_key` column's weight is 10 and `content` column's weight is 1. This weight allocation means `_key` column value is more important rather than `content` column value. In this example, title of blog entry is more important rather thatn content of blog entry.

You can also specify score function. See Scorer for details.

Note that score function isn't related to scorer parameter.

#### 7.3.54.4.2.2. `query`

Specifies the query text. Normally, it is used for fulltext search with `match_columns` parameter. `query` parameter is designed for a fulltext search form in a Web page. A query text should be formatted in Query syntax. The syntax is similar to common search form like Google's search form. For example, `word1 word2` means that groonga searches records that contain both `word1` and `word2`. `word1 OR word2` means that groogna searches records that contain either `word1` or `word2`.

Here is a simple logical and search example.

Execution example:

    select Entries --match_columns content --query "fast groonga"
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         1
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command searches records that contain two words `fast` and `groonga` in `content` column value from `Entries` table.

Here is a simple logical or search example.

Execution example:

    select Entries --match_columns content --query "groonga OR mroonga"
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command searches records that contain one of two words `groonga` or `mroonga` in `content` column value from `Entries` table.

See Query syntax for other syntax.

It can be used for not only fulltext search but also other conditions. For example, `column:value` means the value of `column` column is equal to `value`. `column:<value` means the value of `column` column is less than `value`.

Here is a simple equality operator search example.

Execution example:

    select Entries --query _key:Groonga
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         1
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command searches records that `_key` column value is `Groonga` from `Entries` table.

Here is a simple less than operator search example.

Execution example:

    select Entries --query n_likes:<11
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         4
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         1,
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         5,
    #         "Hello"
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         4,
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         3,
    #         "Senna"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command searches records that `n_likes` column value is less than `11` from `Entries` table.

See Query syntax for other operations.

#### 7.3.54.4.2.3. `filter`

Specifies the filter text. Normally, it is used for complex search conditions. `filter` can be used with `query` parameter. If both `filter` and `query` are specified, there are conbined with logical and. It means that matched records should be matched against both `filter` and `query`.

`filter` parameter is designed for complex conditions. A filter text should be formatted in Script syntax. The syntax is similar to ECMAScript. For example, `column == "value"` means that the value of `column` column is equal to `"value"`. `column < value` means that the value of `column` column is less than `value`.

Here is a simple equality operator search example.

Execution example:

    select Entries --filter '_key == "Groonga"'
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         1
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command searches records that `_key` column value is `Groonga` from `Entries` table.

Here is a simple less than operator search example.

Execution example:

    select Entries --filter 'n_likes < 11'
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         4
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         1,
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         5,
    #         "Hello"
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         4,
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         3,
    #         "Senna"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command searches records that `n_likes` column value is less than `11` from `Entries` table.

See Script syntax for other operators.

### 7.3.54.4.3. Advanced search parameters

#### 7.3.54.4.3.1. `match_escalation_threshold`

Specifies threshold to determine whether search storategy escalation is used or not. The threshold is compared against the number of matched records. If the number of matched records is equal to or less than the threshold, the search storategy escalation is used. See 検索 about the search storategy escalation.

The default threshold is 0. It means that search storategy escalation is used only when no records are matched.

The default threshold can be customized by one of the followings.

> - `--with-match-escalation-threshold` option of configure
> - `--match-escalation-threshold` option of groogna command
> - `match-escalation-threshold` configuration item in configuration file

Here is a simple `match_escalation_threshold` usage example. The first `select` doesn't have `match_escalation_threshold` parameter. The second `select` has `match_escalation_threshold` parameter.

Execution example:

    select Entries --match_columns content --query groo
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         1
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ]
    #     ]
    #   ]
    # ]
    select Entries --match_columns content --query groo --match_escalation_threshold -1
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         0
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ]
    #     ]
    #   ]
    # ]

The first `select` command searches records that contain a word `groo` in `content` column value from `Entries` table. But no records are matched because the `TokenBigram` tokenizer tokenizes `groonga` to `groonga` not `gr|ro|oo|on|ng|ga`. (The `TokenBigramSplitSymbolAlpha` tokenizer tokenizes `groonga` to `gr|ro|oo|on|ng|ga`. See Tokenizers for details.) It means that `groonga` is indexed but `groo` isn't indexed. So no records are matched against `groo` by exact match. In the case, the search storategy escalation is used because the number of matched records (0) is equal to `match_escalation_threshold` (0). One record is matched against `groo` by unsplit search.

The second `select` command also searches records that contain a word `groo` in `content` column value from `Entries` table. And it also doesn't found matched records. In this case, the search storategy escalation is not used because the number of matched records (0) is larger than `match_escalation_threshold` (-1). So no more searches aren't executed. And no records are matched.

#### 7.3.54.4.3.2. `query_expansion`

Deprecated since version 3.0.2: Use query_expander instead.

#### 7.3.54.4.3.3. `query_flags`

It customs `query` parameter syntax. You cannot update column value by `query` parameter by default. But if you specify `ALLOW_COLUMN|ALLOW_UPDATE` as `query_flags`, you can update column value by `query`.

Here are available values:

- `ALLOW_PRAGMA`
- `ALLOW_COLUMN`
- `ALLOW_UPDATE`
- `ALLOW_LEADING_NOT`
- `NONE`

`ALLOW_PRAGMA` enables pragma at the head of `query`. This is not implemented yet.

`ALLOW_COLUMN` enables search againt columns that are not included in `match_columns`. To specify column, there are `COLUMN:...` syntaxes.

`ALLOW_UPDATE` enables column update by `query` with `COLUMN:=NEW_VALUE` syntax. `ALLOW_COLUMN` is also required to update column because the column update syntax specifies column.

`ALLOW_LEADING_NOT` enables leading NOT condition with `-WORD` syntax. The query searches records that doesn't match `WORD`. Leading NOT condition query is heavy query in many cases because it matches many records. So this flag is disabled by default. Be careful about it when you use the flag.

`NONE` is just ignores. You can use `NONE` for specifying no flags.

They can be combined by separated `|` such as `ALLOW_COLUMN|ALLOW_UPDATE`.

The default value is `ALLOW_PRAGMA|ALLOW_COLUMN`.

Here is a usage example of `ALLOW_COLUMN`.

Execution example:

    select Entries --query content:@mroonga --query_flags ALLOW_COLUMN
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         1
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command searches records that contain `mroonga` in `content` column value from `Entries` table.

Here is a usage example of `ALLOW_UPDATE`.

Execution example:

    table_create Users TABLE_HASH_KEY ShortText
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    column_create Users age COLUMN_SCALAR UInt32
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    load --table Users
    [
    {"_key": "alice", "age": 18},
    {"_key": "bob",   "age": 20}
    ]
    # [[0, 1337566253.89858, 0.000355720520019531], 2]
    select Users --query age:=19 --query_flags ALLOW_COLUMN|ALLOW_UPDATE
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "age",
    #           "UInt32"
    #         ]
    #       ],
    #       [
    #         1,
    #         "alice",
    #         19
    #       ],
    #       [
    #         2,
    #         "bob",
    #         19
    #       ]
    #     ]
    #   ]
    # ]
    select Users
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "age",
    #           "UInt32"
    #         ]
    #       ],
    #       [
    #         1,
    #         "alice",
    #         19
    #       ],
    #       [
    #         2,
    #         "bob",
    #         19
    #       ]
    #     ]
    #   ]
    # ]

The first `select` command sets `age` column value of all records to `19`. The second `select` command outputs updated `age` column values.

Here is a usage example of `ALLOW_LEADING_NOT`.

Execution example:

    select Entries --match_columns content --query -mroonga --query_flags ALLOW_LEADING_NOT
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         4
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         1,
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         5,
    #         "Hello"
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         4,
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         3,
    #         "Senna"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command searches records that don't contain `mroonga` in `content` column value from `Entries` table.

Here is a usage example of `NONE`.

Execution example:

    select Entries --match_columns content --query 'mroonga OR _key:Groonga' --query_flags NONE
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         1
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command searches records that contain one of two words `mroonga` or `_key:Groonga` in `content` from `Entries` table. Note that `_key:Groonga` doesn't mean that the value of `_key` column is equal to `Groonga`. Because `ALLOW_COLUMN` flag is not specified.

See also Query syntax.

#### 7.3.54.4.3.4. `query_expander`

It's for query expansion. Query expansion substitutes specific words to another words in query. Nomally, it's used for synonym search.

It specifies a column that is used to substitute `query` parameter value. The format of this parameter value is "`${TABLE}.${COLUMN}`". For example, "`Terms.synonym`" specifies `synonym` column in `Terms` table.

Table for query expansion is called "substitution table". Substitution table's key must be `ShortText`. So array table (`TABLE_NO_KEY`) can't be used for query expansion. Because array table doesn't have key.

Column for query expansion is called "substitution column". Substitution column's value type must be `ShortText`. Column type must be vector (`COLUMN_VECTOR`).

Query expansion substitutes key of substitution table in query with values in substitution column. If a word in `query` is a key of substitution table, the word is substituted with substitution column value that is associated with the key. Substition isn't performed recursively. It means that substitution target words in substituted query aren't substituted.

Here is a sample substitution table to show a simple `query_expander` usage example.

Execution example:

    table_create Thesaurus TABLE_PAT_KEY ShortText --normalizer NormalizerAuto
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    column_create Thesaurus synonym COLUMN_VECTOR ShortText
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    load --table Thesaurus
    [
    {"_key": "mroonga", "synonym": ["mroonga", "tritonn", "groonga mysql"]},
    {"_key": "groonga", "synonym": ["groonga", "senna"]}
    ]
    # [[0, 1337566253.89858, 0.000355720520019531], 2]

`Thesaurus` substitution table has two synonyms, `"mroonga"` and `"groonga"`. If an user searches with `"mroonga"`, Groonga searches with `"((mroonga) OR (tritonn) OR (groonga mysql))"`. If an user searches with `"groonga"`, Groonga searches with `"((groonga) OR (senna))"`.

Normally, it's good idea that substitution table uses a normalizer. For example, if normalizer is used, substitute target word is matched in case insensitive manner. See Normalizers for available normalizers.

Note that those synonym values include the key value such as `"mroonga"` and `"groonga"`. It's recommended that you include the key value. If you don't include key value, substituted value doesn't include the original substitute target value. Normally, including the original value is better search result. If you have a word that you don't want to be searched, you should not include the original word. For example, you can implement "stop words" by an empty vector value.

Here is a simple `query_expander` usage example.

Execution example:

    select Entries --match_columns content --query "mroonga"
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         1
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ]
    #     ]
    #   ]
    # ]
    select Entries --match_columns content --query "mroonga" --query_expander Thesaurus.synonym
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ]
    #     ]
    #   ]
    # ]
    select Entries --match_columns content --query "((mroonga) OR (tritonn) OR (groonga mysql))"
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ]
    #     ]
    #   ]
    # ]

The first `select` command doesn't use query expansion. So a record that has `"tritonn"` isn't found. The second `select` command uses query expansion. So a record that has `"tritonn"` is found. The third `select` command doesn't use query expansion but it is same as the second `select` command. The third one uses expanded query.

Each substitute value can contain any Query syntax syntax such as `(...)` and `OR`. You can use complex substitution by using those syntax.

Here is a complex substitution usage example that uses query syntax.

Execution example:

    load --table Thesaurus
    [
    {"_key": "popular", "synonym": ["popular", "n_likes:>=10"]}
    ]
    # [[0, 1337566253.89858, 0.000355720520019531], 1]
    select Entries --match_columns content --query "popular" --query_expander Thesaurus.synonym
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ]
    #     ]
    #   ]
    # ]

The `load` command registers a new synonym `"popular"`. It is substituted with `((popular) OR (n_likes:>=10))`. The substituted query means that "popular" is containing the word "popular" or 10 or more liked entries.

The `select` command outputs records that `n_likes` column value is equal to or more than `10` from `Entries` table.

### 7.3.54.4.4. Output related parameters

#### 7.3.54.4.4.1. `output_columns`

Specifies output columns separated by `,`.

Here is a simple `output_columns` usage example.

Execution example:

    select Entries --output_columns '_id, _key' --limit 1
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         1,
    #         "The first post!"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command just outputs `_id` and `_key` column values.

`*` is a special value. It means that all columns that are not Pseudo column.

Here is a `*` usage example.

Execution example:

    select Entries --output_columns '_key, *' --limit 1
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         5,
    #         "Hello"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command outputs `_key` pseudo column, `content` column and `n_likes` column values but doesn't output `_id` pseudo column value.

The default value is `_id, _key, *`. It means that all column values except `_score` are outputted.

#### 7.3.54.4.4.2. `sortby`

Deprecated since version 6.0.3: Use sort_keys instead.

#### 7.3.54.4.4.3. `sort_keys`

Specifies sort keys separated by `,`. Each sort key is column name.

Here is a simple `sort_keys` usage example.

Execution example:

    select Entries --sort_keys 'n_likes, _id'
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         4,
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         3,
    #         "Senna"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ],
    #       [
    #         1,
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         5,
    #         "Hello"
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command sorts by `n_likes` column value in ascending order. For records that has the same `n_likes` are sorted by `_id` in ascending order. `"Good-bye Senna"` and `"Good-bye Tritonn"` are the case.

If you want to sort in descending order, add `-` before column name.

Here is a descending order `sort_keys` usage example.

Execution example:

    select Entries --sort_keys '-n_likes, _id'
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         1,
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         5,
    #         "Hello"
    #       ],
    #       [
    #         4,
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         3,
    #         "Senna"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command sorts by `n_likes` column value in descending order. But ascending order is used for sorting by `_id`.

You can use `_score` pseudo column in `sort_keys` if you use `query` or `filter` parameter.

Execution example:

    select Entries --match_columns content --query fast --sort_keys -_score --output_columns '_key, _score'
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_score",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "Mroonga",
    #         2
    #       ],
    #       [
    #         "Groonga",
    #         1
    #       ]
    #     ]
    #   ]
    # ]

The `select` command sorts matched records by hit score in descending order and outputs record key and hit score.

If you use `_score` without `query` nor `filter` parameters, it's just ignored but get a warning in log file.

#### 7.3.54.4.4.4. `offset`

Specifies offset to determine output records range. Offset is zero-based. `--offset 1` means output range is started from the 2nd record.

Execution example:

    select Entries --sortby _id --offset 3 --output_columns _key
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         "Good-bye Senna"
    #       ],
    #       [
    #         "Good-bye Tritonn"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command outputs from the 4th record.

You can specify negative value. It means that `the number of matched records + offset`. If you have 3 matched records and specify `--offset -2`, you get records from the 2nd (`3 + -2 = 1`. `1` means 2nd. Remember that offset is zero-based.) record to the 3rd record.

Execution example:

    select Entries --sortby _id --offset -2 --output_columns _key
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         "Good-bye Senna"
    #       ],
    #       [
    #         "Good-bye Tritonn"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command outputs from the 4th record because the total number of records is `5`.

The default value is `0`.

#### 7.3.54.4.4.5. `limit`

Specifies the max number of output records. If the number of matched records is less than `limit`, all records are outputted.

Here is a simple `limit` usage example.

Execution example:

    select Entries --sortby _id --offset 2 --limit 3 --output_columns _key
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         "Mroonga"
    #       ],
    #       [
    #         "Good-bye Senna"
    #       ],
    #       [
    #         "Good-bye Tritonn"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command outputs the 3rd, the 4th and the 5th records.

You can specify negative value. It means that `the number of matched records + limit + 1`. For example, `--limit -1` outputs all records. It's very useful value to show all records.

Here is a simple negative `limit` value usage example.

Execution example:

    select Entries --limit -1
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         1,
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         5,
    #         "Hello"
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ],
    #       [
    #         4,
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         3,
    #         "Senna"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command outputs all records.

The default value is `10`.

#### 7.3.54.4.4.6. `scorer`

TODO: write in English and add example.

検索条件にマッチする全てのレコードに対して適用するgrn_exprをscript形式で指定します。

scorerは、検索処理が完了し、ソート処理が実行される前に呼び出されます。従って、各レコードのスコアを操作する式を指定しておけば、検索結果のソート順序をカスタマイズできるようになります。

### 7.3.54.4.5. Drilldown related parameters

This section describes basic drilldown related parameters. Advanced drilldown related parameters are described in another section.

#### 7.3.54.4.5.1. `drilldown`

Specifies keys for grouping separated by `,`.

Matched records by specified search conditions are grouped by each key. If you specify no search condition, all records are grouped by each key.

Here is a simple `drilldown` example:

Execution example:

    select Entries \
      --output_columns _key,tag \
      --drilldown tag
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         "The first post!",
    #         "Hello"
    #       ],
    #       [
    #         "Groonga",
    #         "Groonga"
    #       ],
    #       [
    #         "Mroonga",
    #         "Groonga"
    #       ],
    #       [
    #         "Good-bye Senna",
    #         "Senna"
    #       ],
    #       [
    #         "Good-bye Tritonn",
    #         "Senna"
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "Hello",
    #         1
    #       ],
    #       [
    #         "Groonga",
    #         2
    #       ],
    #       [
    #         "Senna",
    #         2
    #       ]
    #     ]
    #   ]
    # ]

The `select` command outputs the following information:

> - There is one record that has "Hello" tag.
> - There is two records that has "Groonga" tag.
> - There is two records that has "Senna" tag.

Here is a `drilldown` with search condition example:

Execution example:

    select Entries \
      --output_columns _key,tag \
      --filter 'n_likes >= 5' \
      --drilldown tag
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         "The first post!",
    #         "Hello"
    #       ],
    #       [
    #         "Groonga",
    #         "Groonga"
    #       ],
    #       [
    #         "Mroonga",
    #         "Groonga"
    #       ]
    #     ],
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "Hello",
    #         1
    #       ],
    #       [
    #         "Groonga",
    #         2
    #       ]
    #     ]
    #   ]
    # ]

The `select` command outputs the following information:

> - In records that have 5 or larger as `n_likes` value:
>   - There is one record that has "Hello" tag.
>   - There is two records that has "Groonga" tag.

Here is a `drilldown` with multiple group keys example:

Execution example:

    select Entries \
      --limit 0 \
      --output_column _id \
      --drilldown tag,n_likes
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "Hello",
    #         1
    #       ],
    #       [
    #         "Groonga",
    #         2
    #       ],
    #       [
    #         "Senna",
    #         2
    #       ]
    #     ],
    #     [
    #       [
    #         4
    #       ],
    #       [
    #         [
    #           "_key",
    #           "UInt32"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         5,
    #         1
    #       ],
    #       [
    #         10,
    #         1
    #       ],
    #       [
    #         15,
    #         1
    #       ],
    #       [
    #         3,
    #         2
    #       ]
    #     ]
    #   ]
    # ]

The `select` command outputs the following information:

> - About `tag`:
>   - There is one record that has "Hello" tag.
>   - There is two records that has "Groonga" tag.
>   - There is two records that has "Senna" tag.
> - About `n_likes`:
>   - There is one record that has "Hello" tag.
>   - There is two records that has "Groonga" tag.
>   - There is two records that has "Senna" tag.

#### 7.3.54.4.5.2. `drilldown_sortby`

Deprecated since version 6.0.3: Use drilldown_sort_keys instead.

#### 7.3.54.4.5.3. `drilldown_sort_keys`

Specifies sort keys for drilldown outputs separated by `,`. Each sort key is column name.

You can refer the number of grouped records by `_nsubrecs` Pseudo column.

Here is a simple `drilldown_sortby` example:

Execution example:

    select Entries \
      --limit 0 \
      --output_column _id \
      --drilldown 'tag, n_likes' \
      --drilldown_sortby '-_nsubrecs, _key'
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "Groonga",
    #         2
    #       ],
    #       [
    #         "Senna",
    #         2
    #       ],
    #       [
    #         "Hello",
    #         1
    #       ]
    #     ],
    #     [
    #       [
    #         4
    #       ],
    #       [
    #         [
    #           "_key",
    #           "UInt32"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         3,
    #         2
    #       ],
    #       [
    #         5,
    #         1
    #       ],
    #       [
    #         10,
    #         1
    #       ],
    #       [
    #         15,
    #         1
    #       ]
    #     ]
    #   ]
    # ]

Drilldown result is sorted by the number of grouped records (= `_nsubrecs` ) in descending order. If there are grouped results that the number of records in the group are the same, these grouped results are sorted by grouped key (= `_key` ) in ascending order.

The sort keys are used in all group keys specified in `drilldown`:

Execution example:

    select Entries \
      --limit 0 \
      --output_column _id \
      --drilldown 'tag, n_likes' \
      --drilldown_sortby '-_nsubrecs, _key'
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "Groonga",
    #         2
    #       ],
    #       [
    #         "Senna",
    #         2
    #       ],
    #       [
    #         "Hello",
    #         1
    #       ]
    #     ],
    #     [
    #       [
    #         4
    #       ],
    #       [
    #         [
    #           "_key",
    #           "UInt32"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         3,
    #         2
    #       ],
    #       [
    #         5,
    #         1
    #       ],
    #       [
    #         10,
    #         1
    #       ],
    #       [
    #         15,
    #         1
    #       ]
    #     ]
    #   ]
    # ]

The same sort keys are used in `tag` drilldown and `n_likes` drilldown.

If you want to use different sort keys for each drilldown, use Advanced drilldown related parameters.

#### 7.3.54.4.5.4. `drilldown_output_columns`

Specifies output columns for drilldown separated by `,`.

Here is a `drilldown_output_columns` example:

Execution example:

    select Entries \
      --limit 0 \
      --output_column _id \
      --drilldown tag \
      --drilldown_output_columns _key
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         "Hello"
    #       ],
    #       [
    #         "Groonga"
    #       ],
    #       [
    #         "Senna"
    #       ]
    #     ]
    #   ]
    # ]

The `select` command just outputs grouped key.

If grouped key is a referenced type column (= column that its type is a table), you can access column of the table referenced by the referenced type column.

Here are a schema definition and sample data to show drilldown against referenced type column:

Execution example:

    table_create Tags TABLE_HASH_KEY ShortText --normalizer NormalizerAuto
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    column_create Tags label COLUMN_SCALAR ShortText
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    column_create Tags priority COLUMN_SCALAR Int32
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    table_create Items TABLE_HASH_KEY ShortText
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    column_create Items tag COLUMN_SCALAR Tags
    # [[0, 1337566253.89858, 0.000355720520019531], true]
    load --table Tags
    [
    {"_key": "groonga", label: "Groonga", priority: 10},
    {"_key": "mroonga", label: "Mroonga", priority: 5}
    ]
    # [[0, 1337566253.89858, 0.000355720520019531], 2]
    load --table Items
    [
    {"_key": "A", "tag": "groonga"},
    {"_key": "B", "tag": "groonga"},
    {"_key": "C", "tag": "mroonga"}
    ]
    # [[0, 1337566253.89858, 0.000355720520019531], 3]

`Tags` table is a referenced table. `Items.tag` is a referenced type column.

You can refer `Tags.label` by `label` in `drilldown_output_columns`:

Execution example:

    select Items \
      --limit 0 \
      --output_column _id \
      --drilldown tag \
      --drilldown_output_columns '_key, label'
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "tag",
    #           "Tags"
    #         ]
    #       ]
    #     ],
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "label",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         "groonga",
    #         "Groonga"
    #       ],
    #       [
    #         "mroonga",
    #         "Mroonga"
    #       ]
    #     ]
    #   ]
    # ]

You can use `*` to refer all columns in referenced table (= `Tags`):

Execution example:

    select Items \
      --limit 0 \
      --output_column _id \
      --drilldown tag \
      --drilldown_output_columns '_key, *'
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "tag",
    #           "Tags"
    #         ]
    #       ]
    #     ],
    #     [
    #       [
    #         2
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "label",
    #           "ShortText"
    #         ],
    #         [
    #           "priority",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "groonga",
    #         "Groonga",
    #         10
    #       ],
    #       [
    #         "mroonga",
    #         "Mroonga",
    #         5
    #       ]
    #     ]
    #   ]
    # ]

`*` is expanded to `label, priority`.

The default value of `drilldown_output_columns` is `_key, _nsubrecs`. It means that grouped key and the number of records in the group are output.

You can use more Pseudo column in `drilldown_output_columns` such as `_max`, `_min`, `_sum` and `_avg` when you use drilldown_calc_types. See `drilldown_calc_types` document for details.

#### 7.3.54.4.5.5. `drilldown_offset`

Specifies offset to determine range of drilldown output records. Offset is zero-based. `--drilldown_offset 1` means output range is started from the 2nd record.

Here is a `drilldown_offset` example:

Execution example:

    select Entries \
      --limit 0 \
      --output_column _id \
      --drilldown tag \
      --drilldown_sortby _key \
      --drilldown_offset 1
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "Hello",
    #         1
    #       ],
    #       [
    #         "Senna",
    #         2
    #       ]
    #     ]
    #   ]
    # ]

The `select` command outputs from the 2nd record.

You can specify negative value. It means that `the number of grouped results + offset`. If you have 3 grouped results and specify `--drilldown_offset -2`, you get grouped results from the 2st (`3 + -2 = 1`. `1` means 2nd. Remember that offset is zero-based.) grouped result to the 3rd grouped result.

Execution example:

    select Entries \
      --limit 0 \
      --output_column _id \
      --drilldown tag \
      --drilldown_sortby _key \
      --drilldown_offset -2
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "Hello",
    #         1
    #       ],
    #       [
    #         "Senna",
    #         2
    #       ]
    #     ]
    #   ]
    # ]

The `select` command outputs from the 2nd grouped result because the total number of grouped results is `3`.

The default value of `drilldown_offset` is `0`.

#### 7.3.54.4.5.6. `drilldown_limit`

Specifies the max number of groups in a drilldown. If the number of groups is less than `drilldown_limit`, all groups are outputted.

Here is a `drilldown_limit` example:

Execution example:

    select Entries \
      --limit 0 \
      --output_column _id \
      --drilldown tag \
      --drilldown_sortby _key \
      --drilldown_offset 1 \
      --drilldown_limit 2
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "Hello",
    #         1
    #       ],
    #       [
    #         "Senna",
    #         2
    #       ]
    #     ]
    #   ]
    # ]

The `select` command outputs the 2rd and the 3rd groups.

You can specify negative value. It means that `the number of groups + drilldown_limit + 1`. For example, `--drilldown_limit -1` outputs all groups. It's very useful value to show all groups.

Here is a negative `drilldown_limit` value example.

Execution example:

    select Entries \
      --limit 0 \
      --output_column _id \
      --drilldown tag \
      --drilldown_sortby _key \
      --drilldown_limit -1
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "Groonga",
    #         2
    #       ],
    #       [
    #         "Hello",
    #         1
    #       ],
    #       [
    #         "Senna",
    #         2
    #       ]
    #     ]
    #   ]
    # ]

The `select` command outputs all groups.

The default value of `drilldown_limit` is `10`.

#### 7.3.54.4.5.7. `drilldown_calc_types`

Specifies how to calculate (aggregate) values in grouped records by a drilldown. You can specify multiple calculation types separated by "`,`". For example, `MAX,MIN`.

Calculation target values are read from a column of grouped records. The column is specified by drilldown_calc_target.

You can read calculated value by Pseudo column such as `_max` and `_min` in drilldown_output_columns.

You can use the following calculation types:

Type name | Pseudo column name | Need drilldown_calc_target | Description
--- | --- | --- | ---
`NONE` | Nothing. | Not needs. | Just ignored.
`COUNT` | `_nsubrecs` | Not needs. | Counting grouped records. It's always enabled. So you don't need to specify it.
`MAX` | `_max` | Needs. | Finding the maximum integer value from integer values in grouped records.
`MIN` | `_min` | Needs. | Finding the minimum integer value from integer values in grouped records.
`SUM` | `_sum` | Needs. | Summing integer values in grouped records.
`AVG` | `_avg` | Needs. | Averaging integer/float values in grouped records.

Here is a `MAX` example:

Execution example:

    select Entries \
      --limit -1 \
      --output_column _id,n_likes \
      --drilldown tag \
      --drilldown_calc_types MAX \
      --drilldown_calc_target n_likes \
      --drilldown_output_columns _key,_max
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         1,
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         5,
    #         "Hello"
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ],
    #       [
    #         4,
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         3,
    #         "Senna"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_max",
    #           "Int64"
    #         ]
    #       ],
    #       [
    #         "Hello",
    #         5
    #       ],
    #       [
    #         "Groonga",
    #         15
    #       ],
    #       [
    #         "Senna",
    #         3
    #       ]
    #     ]
    #   ]
    # ]

The `select` command groups all records by `tag` column value, finding the maximum `n_likes` column value for each group and outputs pairs of grouped key and the maximum `n_likes` column value for the group. It uses `_max` Pseudo column to read the maximum `n_likes` column value.

Here is a `MIN` example:

Execution example:

    select Entries \
      --limit -1 \
      --output_column _id,n_likes \
      --drilldown tag \
      --drilldown_calc_types MIN \
      --drilldown_calc_target n_likes \
      --drilldown_output_columns _key,_min
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         1,
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         5,
    #         "Hello"
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ],
    #       [
    #         4,
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         3,
    #         "Senna"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_min",
    #           "Int64"
    #         ]
    #       ],
    #       [
    #         "Hello",
    #         5
    #       ],
    #       [
    #         "Groonga",
    #         10
    #       ],
    #       [
    #         "Senna",
    #         3
    #       ]
    #     ]
    #   ]
    # ]

The `select` command groups all records by `tag` column value, finding the minimum `n_likes` column value for each group and outputs pairs of grouped key and the minimum `n_likes` column value for the group. It uses `_min` Pseudo column to read the minimum `n_likes` column value.

Here is a `SUM` example:

Execution example:

    select Entries \
      --limit -1 \
      --output_column _id,n_likes \
      --drilldown tag \
      --drilldown_calc_types SUM \
      --drilldown_calc_target n_likes \
      --drilldown_output_columns _key,_sum
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         1,
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         5,
    #         "Hello"
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ],
    #       [
    #         4,
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         3,
    #         "Senna"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_sum",
    #           "Int64"
    #         ]
    #       ],
    #       [
    #         "Hello",
    #         5
    #       ],
    #       [
    #         "Groonga",
    #         25
    #       ],
    #       [
    #         "Senna",
    #         6
    #       ]
    #     ]
    #   ]
    # ]

The `select` command groups all records by `tag` column value, sums all `n_likes` column values for each group and outputs pairs of grouped key and the summed `n_likes` column values for the group. It uses `_sum` Pseudo column to read the summed `n_likes` column values.

Here is a `AVG` example:

Execution example:

    select Entries \
      --limit -1 \
      --output_column _id,n_likes \
      --drilldown tag \
      --drilldown_calc_types AVG \
      --drilldown_calc_target n_likes \
      --drilldown_output_columns _key,_avg
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         1,
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         5,
    #         "Hello"
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ],
    #       [
    #         4,
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         3,
    #         "Senna"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_avg",
    #           "Float"
    #         ]
    #       ],
    #       [
    #         "Hello",
    #         5.0
    #       ],
    #       [
    #         "Groonga",
    #         12.5
    #       ],
    #       [
    #         "Senna",
    #         3.0
    #       ]
    #     ]
    #   ]
    # ]

The `select` command groups all records by `tag` column value, averages all `n_likes` column values for each group and outputs pairs of grouped key and the averaged `n_likes` column values for the group. It uses `_avg` Pseudo column to read the averaged `n_likes` column values.

Here is an example that uses all calculation types:

Execution example:

    select Entries \
      --limit -1 \
      --output_column _id,n_likes \
      --drilldown tag \
      --drilldown_calc_types MAX,MIN,SUM,AVG \
      --drilldown_calc_target n_likes \
      --drilldown_output_columns _key,_nsubrecs,_max,_min,_sum,_avg
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         1,
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         5,
    #         "Hello"
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ],
    #       [
    #         4,
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         3,
    #         "Senna"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ]
    #     ],
    #     [
    #       [
    #         3
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "_nsubrecs",
    #           "Int32"
    #         ],
    #         [
    #           "_max",
    #           "Int64"
    #         ],
    #         [
    #           "_min",
    #           "Int64"
    #         ],
    #         [
    #           "_sum",
    #           "Int64"
    #         ],
    #         [
    #           "_avg",
    #           "Float"
    #         ]
    #       ],
    #       [
    #         "Hello",
    #         1,
    #         5,
    #         5,
    #         5,
    #         5.0
    #       ],
    #       [
    #         "Groonga",
    #         2,
    #         15,
    #         10,
    #         25,
    #         12.5
    #       ],
    #       [
    #         "Senna",
    #         2,
    #         3,
    #         3,
    #         6,
    #         3.0
    #       ]
    #     ]
    #   ]
    # ]

The `select` command specifies multiple calculation types separated by "`,`" like `MAX,MIN,SUM,AVG`. You can use `_nsubrecs` Pseudo column in drilldown_output_columns without specifying `COUNT` in `drilldown_calc_types`. Because `COUNT` is always enabled.

The default value of `drilldown_calc_types` is `NONE`. It means that only `COUNT` is enabled. Because `NONE` is just ignored and `COUNT` is always enabled.

#### 7.3.54.4.5.8. `drilldown_calc_target`

Specifies the target column for drilldown_calc_types.

If you specify a calculation type that needs a target column such as `MAX` in drilldown_calc_types but you omit `drilldown_calc_target`, the calculation result is always `0`.

You can specify only one column name like `--drilldown_calc_target n_likes`. You can't specify multiple column name like `--drilldown_calc_target _key,n_likes`.

You can use referenced value from the target record by combining "`.`" like `--drilldown_calc_target reference_column.nested_reference_column.value`.

See drilldown_calc_types to know how to use `drilldown_calc_target`.

The default value of `drilldown_calc_target` is `null`. It means that no calculation target column is specified.

### 7.3.54.4.6. Advanced drilldown related parameters

You can get multiple drilldown results by specifying multiple group keys by drilldown. But you need to use the same configuration for all drilldowns. For example, drilldown_output_columns is used by all drilldowns.

You can use a configuration for each drilldown by the following parameters:

> - `drilldowns[${LABEL}].keys`
> - `drilldowns[${LABEL}].sortby`
> - `drilldowns[${LABEL}].output_columns`
> - `drilldowns[${LABEL}].offset`
> - `drilldowns[${LABEL}].limit`
> - `drilldowns[${LABEL}].calc_types`
> - `drilldowns[${LABEL}].calc_target`

`${LABEL}` is a variable. You can use the following characters for `${LABEL}`:

> - Alphabets
> - Digits
> - `.`
> - `_`

Note

You can use more characters but it's better that you use only these characters.

Parameters that has the same `${LABEL}` value are grouped. Grouped parameters are used for one drilldown.

For example, there are 2 groups for the following parameters:

> - `--drilldowns[label1].keys _key`
> - `--drilldowns[label1].output_columns _nsubrecs`
> - `--drilldowns[label2].keys tag`
> - `--drilldowns[label2].output_columns _key,_nsubrecs`

`drilldowns[label1].keys` and `drilldowns[label1].output_columns` are grouped. `drilldowns[label2].keys` and `drilldowns[label2].output_columns` are also grouped.

In `label1` group, `_key` is used for group key and `_nsubrecs` is used for output columns.

In `label2` group, `tag` is used for group key and `_key,_nsubrecs` is used for output columns.

See document for corresponding `drilldown_XXX` parameter to know how to use it for the following parameters:

> - `drilldowns[${LABEL}].sortby`: drilldown_sortby
> - `drilldowns[${LABEL}].offset`: drilldown_offset
> - `drilldowns[${LABEL}].limit`: drilldown_limit
> - `drilldowns[${LABEL}].calc_types`: drilldown_calc_types
> - `drilldowns[${LABEL}].calc_target`: drilldown_calc_target

The following parameters are needed more description:

> - `drilldowns[${LABEL}].keys`
> - `drilldowns[${LABEL}].output_columns`

Output format is different a bit. It's also needed more description.

#### 7.3.54.4.6.1. `drilldowns[${LABEL}].keys`

drilldown can specify multiple keys for multiple drilldowns. But it can't specify multiple keys for one drilldown.

`drilldowns[${LABEL}].keys` can't specify multiple keys for multiple drilldowns. But it can specify multiple keys for one drilldown.

You can specify multiple keys separated by "`,`".

Here is an example to group by multiple keys, `tag` and `n_likes` column values:

Execution example:

    select Entries \
      --limit -1 \
      --output_column tag,n_likes \
      --drilldown[tag.n_likes].keys tag,n_likes \
      --drilldown[tag.n_likes].output_columns _value.tag,_value.n_likes,_nsubrecs
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         1,
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         5,
    #         "Hello"
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ],
    #       [
    #         4,
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         3,
    #         "Senna"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ]
    #     ],
    #     {
    #       "tag.n_likes": [
    #         [
    #           4
    #         ],
    #         [
    #           [
    #             "tag",
    #             "ShortText"
    #           ],
    #           [
    #             "n_likes",
    #             "UInt32"
    #           ],
    #           [
    #             "_nsubrecs",
    #             "Int32"
    #           ]
    #         ],
    #         [
    #           "Hello",
    #           5,
    #           1
    #         ],
    #         [
    #           "Groonga",
    #           10,
    #           1
    #         ],
    #         [
    #           "Groonga",
    #           15,
    #           1
    #         ],
    #         [
    #           "Senna",
    #           3,
    #           2
    #         ]
    #       ]
    #     }
    #   ]
    # ]

`tag.n_likes` is used as the label for the drilldown parameters group. You can refer grouped keys by `_value.${KEY_NAME}` syntax in drilldowns[${LABEL}].output_columns. `${KEY_NAME}` is a column name to be used by group key. `tag` and `n_likes` are `${KEY_NAME}` in this case.

Note that you can't use `_value.${KEY_NAME}` syntax when you just specify one key as `drilldowns[${LABEL}].keys` like `--drilldowns[tag].keys tag`. You should use `_key` for the case. It's the same rule in drilldown_output_columns.

#### 7.3.54.4.6.2. `drilldowns[${LABEL}].output_columns`

It's almost same as drilldown_output_columns. The difference between drilldown_output_columns and `drilldowns[${LABEL}].output_columns` is how to refer group keys.

drilldown_output_columns uses `_key` Pseudo column to refer group key. `drilldowns[${LABEL}].output_columns` also uses `_key` Pseudo column to refer group key when you specify only one group key by drilldowns[${LABEL}].keys.

Here is an example to refer single group key by `_key` Pseudo column:

Execution example:

    select Entries \
      --limit 0 \
      --output_column _id \
      --drilldown[tag.n_likes].keys tag,n_likes \
      --drilldown[tag.n_likes].output_columns _value.tag,_value.n_likes
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ]
    #     ],
    #     {
    #       "tag.n_likes": [
    #         [
    #           4
    #         ],
    #         [
    #           [
    #             "tag",
    #             "ShortText"
    #           ],
    #           [
    #             "n_likes",
    #             "UInt32"
    #           ]
    #         ],
    #         [
    #           "Hello",
    #           5
    #         ],
    #         [
    #           "Groonga",
    #           10
    #         ],
    #         [
    #           "Groonga",
    #           15
    #         ],
    #         [
    #           "Senna",
    #           3
    #         ]
    #       ]
    #     }
    #   ]
    # ]

But you can't refer each group key by `_key` Pseudo column in `drilldowns[${LABEL}].output_columns`. You need to use `_value.${KEY_NAME}` syntax. `${KEY_NAME}` is a column name that is used for group key in drilldowns[${LABEL}].keys.

Here is an example to refer each group key in multiple group keys by `_value.${KEY_NAME}` syntax:

Execution example:

    select Entries \
      --limit 0 \
      --output_column _id \
      --drilldown[tag.n_likes].keys tag,n_likes \
      --drilldown[tag.n_likes].output_columns _value.tag,_value.n_likes
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ]
    #     ],
    #     {
    #       "tag.n_likes": [
    #         [
    #           4
    #         ],
    #         [
    #           [
    #             "tag",
    #             "ShortText"
    #           ],
    #           [
    #             "n_likes",
    #             "UInt32"
    #           ]
    #         ],
    #         [
    #           "Hello",
    #           5
    #         ],
    #         [
    #           "Groonga",
    #           10
    #         ],
    #         [
    #           "Groonga",
    #           15
    #         ],
    #         [
    #           "Senna",
    #           3
    #         ]
    #       ]
    #     }
    #   ]
    # ]

Tip

Why `_value.${KEY_NAME}` syntax?

It's implementation specific information.

`_key` is a vector value. The vector value is consists of all group keys. You can see byte sequence of the vector value by referring `_key` in `drilldowns[${LABEL}].output_columns`.

There is one grouped record in `_value` to refer each grouped values when you specify multiple group keys to drilldowns[${LABEL}].keys. So you can refer each group key by `_value.${KEY_NAME}` syntax.

On the other hand, there is no grouped record in `_value` when you specify only one group key to drilldowns[${LABEL}].keys. So you can't refer group key by `_value.${KEY_NAME}` syntax.

#### 7.3.54.4.6.3. Output format for `drilldowns[${LABEL}]` style

There is a difference in output format between drilldown and drilldowns[${LABEL}].keys. drilldown uses array to output multiple drilldown results. drilldowns[${LABEL}].keys uses pairs of label and drilldown result.

drilldown uses the following output format:

    [
      HEADER,
      [
        SEARCH_RESULT,
        DRILLDOWN_RESULT1,
        DRILLDOWN_RESULT2,
        ...
      ]
    ]

drilldowns[${LABEL}].keys uses the following output format:

    [
      HEADER,
      [
        SEARCH_RESULT,
        {
          "LABEL1": DRILLDOWN_RESULT1,
          "LABEL2": DRILLDOWN_RESULT2,
          ...
        }
      ]
    ]

### 7.3.54.4.7. Cache related parameter

#### 7.3.54.4.7.1. `cache`

Specifies whether caching the result of this query or not.

If the result of this query is cached, the next same query returns response quickly by using the cache.

It doesn't control whether existing cached result is used or not.

Here are available values:

Value | Description
--- | ---
`no` | Don't cache the output of this query.
`yes` | Cache the output of this query. It's the default value.

Here is an example to disable caching the result of this query:

Execution example:

    select Entries --cache no
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_id",
    #           "UInt32"
    #         ],
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "n_likes",
    #           "UInt32"
    #         ],
    #         [
    #           "tag",
    #           "ShortText"
    #         ]
    #       ],
    #       [
    #         1,
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         5,
    #         "Hello"
    #       ],
    #       [
    #         2,
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         10,
    #         "Groonga"
    #       ],
    #       [
    #         3,
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         15,
    #         "Groonga"
    #       ],
    #       [
    #         4,
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         3,
    #         "Senna"
    #       ],
    #       [
    #         5,
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         3,
    #         "Senna"
    #       ]
    #     ]
    #   ]
    # ]

The default value is `yes`.

### 7.3.54.4.8. Score related parameters

There is a score related parameter, `adjuster`.

#### 7.3.54.4.8.1. `adjuster`

Specifies one or more score adjust expressions. You need to use `adjuster` with `query` or `filter`. `adjuster` doesn't work with not searched request.

You can increase score of specific records by `adjuster`. You can use `adjuster` to set high score for important records.

For example, you can use `adjuster` to increase score of records that have `groonga` tag.

Here is the syntax:

    --adjuster "SCORE_ADJUST_EXPRESSION1 + SCORE_ADJUST_EXPRESSION2 + ..."

Here is the `SCORE_ADJUST_EXPRESSION` syntax:

    COLUMN @ "KEYWORD" * FACTOR

Note the following:

> - `COLUMN` must be indexed.
> - `"KEYWORD"` must be a string.
> - `FACTOR` must be a positive integer.

Here is a sample `adjuster` usage example that uses just one `SCORE_ADJUST_EXPRESSION`:

Execution example:

    select Entries \
      --filter true \
      --adjuster 'content @ "groonga" * 5' \
      --output_columns _key,content,_score
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "_score",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         1
    #       ],
    #       [
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         6
    #       ],
    #       [
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         1
    #       ],
    #       [
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         1
    #       ],
    #       [
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         1
    #       ]
    #     ]
    #   ]
    # ]

The `select` command matches all records. Then it applies `adjuster`. The adjuster increases score of records that have `"groonga"` in `Entries.content` column by 5. There is only one record that has `"groonga"` in `Entries.content` column. So the record that its key is `"Groonga"` has score 6 (`= 1 + 5`).

You can omit `FACTOR`. If you omit `FACTOR`, it is treated as 1.

Here is a sample `adjuster` usage example that omits `FACTOR`:

Execution example:

    select Entries \
      --filter true \
      --adjuster 'content @ "groonga"' \
      --output_columns _key,content,_score
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "_score",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         1
    #       ],
    #       [
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         2
    #       ],
    #       [
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         1
    #       ],
    #       [
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         1
    #       ],
    #       [
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         1
    #       ]
    #     ]
    #   ]
    # ]

The `adjuster` in the `select` command doesn't have `FACTOR`. So the factor is treated as 1. There is only one record that has `"groonga"` in `Entries.content` column. So the record that its key is `"Groonga"` has score 2 (`= 1 + 1`).

Here is a sample `adjuster` usage example that uses multiple `SCORE_ADJUST_EXPRESSION`:

Execution example:

    select Entries \
      --filter true \
      --adjuster 'content @ "groonga" * 5 + content @ "started" * 3' \
      --output_columns _key,content,_score
    # [
    #   [
    #     0,
    #     1337566253.89858,
    #     0.000355720520019531
    #   ],
    #   [
    #     [
    #       [
    #         5
    #       ],
    #       [
    #         [
    #           "_key",
    #           "ShortText"
    #         ],
    #         [
    #           "content",
    #           "Text"
    #         ],
    #         [
    #           "_score",
    #           "Int32"
    #         ]
    #       ],
    #       [
    #         "The first post!",
    #         "Welcome! This is my first post!",
    #         1
    #       ],
    #       [
    #         "Groonga",
    #         "I started to use Groonga. It's very fast!",
    #         9
    #       ],
    #       [
    #         "Mroonga",
    #         "I also started to use Mroonga. It's also very fast! Really fast!",
    #         4
    #       ],
    #       [
    #         "Good-bye Senna",
    #         "I migrated all Senna system!",
    #         1
    #       ],
    #       [
    #         "Good-bye Tritonn",
    #         "I also migrated all Tritonn system!",
    #         1
    #       ]
    #     ]
    #   ]
    # ]

The `adjuster` in the `select` command has two `SCORE_ADJUST_EXPRESSION` s. The final increased score is sum of scores of these `SCORE_ADJUST_EXPRESSION` s. All `SCORE_ADJUST_EXPRESSION` s in the `select` command are applied to a record that its key is `"Groonga"`. So the final increased score of the record is sum of scores of all `SCORE_ADJUST_EXPRESSION` s.

The first `SCORE_ADJUST_EXPRESSION` is `content @ "groonga" * 5`. It increases score by 5.

The second `SCORE_ADJUST_EXPRESSION` is `content @ "started" * 3`. It increases score by 3.

The final increased score is 9 (`= 1 + 5 + 3`).

A `SCORE_ADJUST_EXPRESSION` has a factor for `"KEYWORD"`. This means that increased scores of all records that has `"KEYWORD"` are the same value. You can change increase score for each record that has the same `"KEYWORD"`. It is useful to tune search score. See Weight vector column for details.

## 7.3.54.5. Return value

`select` returns response with the following format:

    [
      HEADER,
      [
        SEARCH_RESULT,
        DRILLDOWN_RESULT_1,
        DRILLDOWN_RESULT_2,
        ...,
        DRILLDOWN_RESULT_N
      ]
    ]

If `select` fails, error details are in `HEADER`.

See Output format for `HEADER`.

There are zero or more `DRILLDOWN_RESULT`. If no `drilldown` and `drilldowns[${LABEL}].keys` are specified, they are omitted like the following:

    [
      HEADER,
      [
        SEARCH_RESULT
      ]
    ]

If `drilldown` has two or more keys like `--drilldown "_key, column1, column2"`, multiple `DRILLDOWN_RESULT` exist:

    [
      HEADER,
      [
        SEARCH_RESULT,
        DRILLDOWN_RESULT_FOR_KEY,
        DRILLDOWN_RESULT_FOR_COLUMN1,
        DRILLDOWN_RESULT_FOR_COLUMN2
      ]
    ]

If `drilldowns[${LABEL}].keys` is used, only one `DRILLDOWN_RESULT` exist:

    [
      HEADER,
      [
        SEARCH_RESULT,
        DRILLDOWN_RESULT_FOR_LABELED_DRILLDOWN
      ]
    ]

`DRILLDOWN_RESULT` format is different between `drilldown` and `drilldowns[${LABEL}].keys`. It's described later.

`SEARCH_RESULT` is the following format:

    [
      [N_HITS],
      COLUMNS,
      RECORDS
    ]

See Simple usage for concrete example of the format.

`N_HITS` is the number of matched records before limit is applied.

`COLUMNS` describes about output columns specified by output_columns. It uses the following format:

    [
      [COLUMN_NAME_1, COLUMN_TYPE_1],
      [COLUMN_NAME_2, COLUMN_TYPE_2],
      ...,
      [COLUMN_NAME_N, COLUMN_TYPE_N]
    ]

`COLUMNS` includes one or more output column information. Each output column information includes the followings:

> - Column name as string
> - Column type as string or `null`

Column name is extracted from value specified as output_columns.

Column type is Groonga's type name or `null`. It doesn't describe whether the column value is vector or scalar. You need to determine it by whether real column value is array or not.

See Data types for type details.

`null` is used when column value type isn't determined. For example, function call in output_columns such as `--output_columns "snippet_html(content)"` uses `null`.

Here is an example of `COLUMNS`:

    [
      ["_id",     "UInt32"],
      ["_key",    "ShortText"],
      ["n_likes", "UInt32"],
    ]

`RECORDS` includes column values for each matched record. Included records are selected by offset and limit. It uses the following format:

    [
      [
        RECORD_1_COLUMN_1,
        RECORD_1_COLUMN_2,
        ...,
        RECORD_1_COLUMN_N
      ],
      [
        RECORD_2_COLUMN_1,
        RECORD_2_COLUMN_2,
        ...,
        RECORD_2_COLUMN_N
      ],
      ...
      [
        RECORD_N_COLUMN_1,
        RECORD_N_COLUMN_2,
        ...,
        RECORD_N_COLUMN_N
      ]
    ]

Here is an example `RECORDS`:

    [
      [
        1,
        "The first post!",
        5
      ],
      [
        2,
        "Groonga",
        10
      ],
      [
        3,
        "Mroonga",
        15
      ]
    ]

`DRILLDOWN_RESULT` format is different between `drilldown` and `drilldowns[${LABEL}].keys`.

`drilldown` uses the same format as `SEARCH_RESULT`:

    [
      [N_HITS],
      COLUMNS,
      RECORDS
    ]

And `drilldown` generates one or more `DRILLDOWN_RESULT` when drilldown has one ore more keys.

`drilldowns[${LABEL}].keys` uses the following format. Multiple `drilldowns[${LABEL}].keys` are mapped to one object (key-value pairs):

    {
      "LABEL_1": [
        [N_HITS],
        COLUMNS,
        RECORDS
      ],
      "LABEL_2": [
        [N_HITS],
        COLUMNS,
        RECORDS
      ],
      ...,
      "LABEL_N": [
        [N_HITS],
        COLUMNS,
        RECORDS
      ]
    }

Each `drilldowns[${LABEL}].keys` corresponds to the following:

    "LABEL": [
      [N_HITS],
      COLUMNS,
      RECORDS
    ]

The following value part is the same format as `SEARCH_RESULT`:

    [
      [N_HITS],
      COLUMNS,
      RECORDS
    ]

See also Output format for drilldowns[${LABEL}] style for `drilldowns[${LABEL}]` style drilldown output format.

## 7.3.54.6. See also

> - Query syntax
> - Script syntax